TempDB usage. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

TempDB usage.

Author

Topic

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-15 : 16:50:13

Is there any script which will tell me which query is consuming huge TempDB space(mdf)? I know we can see through Profiler. But i want to track down what is causing it to grow.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-15 : 17:01:38

Try this:

SELECT t1.session_id, t1.request_id, t1.task_alloc,
t1.task_dealloc, t2.sql_handle, t2.statement_start_offset,
t2.statement_end_offset, t2.plan_handle
FROM (Select session_id, request_id,
SUM(internal_objects_alloc_page_count) AS task_alloc,
SUM (internal_objects_dealloc_page_count) AS task_dealloc
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id) AS t1,
sys.dm_exec_requests AS t2
WHERE t1.session_id = t2.session_id
AND (t1.request_id = t2.request_id)
ORDER BY t1.task_alloc DESC

From: http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-15 : 18:06:04

Tried that but didn't gave me SPID info and actual query.Thanks though.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-16 : 03:12:16

You can find the actual query via: SELECT * FROM sys.dm_exec_sql_text(@sql_handle);

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-16 : 13:40:15

I came out with this one:

Select cm.text from
(SELECT t1.session_id, t1.request_id, t1.task_alloc,
t1.task_dealloc, t2.sql_handle, t2.statement_start_offset,
t2.statement_end_offset, t2.plan_handle
FROM (Select session_id, request_id,
SUM(internal_objects_alloc_page_count) AS task_alloc,
SUM (internal_objects_dealloc_page_count) AS task_dealloc
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id) AS t1,
sys.dm_exec_requests AS t2
WHERE t1.session_id = t2.session_id
AND (t1.request_id = t2.request_id)
)c
cross apply sys.dm_exec_sql_text(c.sql_handle)cm

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-16 : 13:45:00

Thanks sodeep. I could've used that query last night when I had perplexing tempdb usage! I'll bookmark this in case it happens again.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources