Post reply

# of tables in TempDB

  • July 6, 2016 at 8:06 am

    #327509

    Hi,

    Is there anyway to get the number of temp tables in TempDB and created by which application? I am working with a customer with high disk contention on TempDB drive.

    Regards,

    Yuji

  • July 6, 2016 at 9:59 am

    #1888726

    Have you tried:

    select * from tempdb.sys.objects

    ?

  • July 7, 2016 at 7:41 am

    #1888916

    select name, object_name(object_id) As ObjName,*

    from tempdb.sys.objects

    where name like '#%'

  • July 7, 2016 at 7:45 am

    #1888920

    You'll be able to see the objects, but you won't be able to tell what they are or where they came from.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • July 7, 2016 at 7:53 am

    #1888924

    Thanks a lot!

  • July 7, 2016 at 8:06 am

    #1888935

    sunita2912 (7/7/2016)

    select name, object_name(object_id) As ObjName,*

    from tempdb.sys.objects

    where name like '#%'

    Is going to give temp table, table variables, cached temp table shells and internal work tables, not just the number of active, in-use temp tables.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 7, 2016 at 11:43 pm

    #1889100

    select left(name, charindex('_',name)-1)

    from tempdb..sysobjects

    where charindex('_',name) > 0 and

    xtype = 'u' and not object_id('tempdb..'+name) is null

  • July 8, 2016 at 1:06 am

    #1889103

    johnwalker10 (7/7/2016)

    select left(name, charindex('_',name)-1)

    from tempdb..sysobjects

    where charindex('_',name) > 0 and

    xtype = 'u' and not object_id('tempdb..'+name) is null

    Thanks, and if I would like to know the top 10 temp tables and the number, how can I do it?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply