July 6, 2016 at 8:06 am
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
Have you tried:
select * from tempdb.sys.objects
?
July 7, 2016 at 7:41 am
select name, object_name(object_id) As ObjName,*
from tempdb.sys.objects
where name like '#%'
July 7, 2016 at 7:45 am
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
Thanks a lot!
July 7, 2016 at 8:06 am
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
July 7, 2016 at 11:43 pm
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
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