SQL Script to Track which Transact-SQL statements are top consumers of tempdb

Leave a comment (0) Go to comments
Whats causing my tempdb to grow – SQL Server 2005 ?

Being a DBA, and when your tempdb size is growing extra ordinary then a DBA has to check, which Transact-SQL statements are top consumers of tempdb.

Here is script, which will help you to figure out, which adhoc TSQL statements are consuming tempdb.

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) and
        (t1.task_alloc + t1.task_dealloc > 0)
ORDER BY t1.task_alloc DESC

This will work in 2005+ versions.


EOF - SQL Script to Track which Transact-SQL statements are top consumers of tempdb, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.