Search

Monday, June 27, 2011

Whats causing my tempdb to grow

The below script will find out which SQL Statement is consuming most TempDB space.


SELECT
    trans1.session_id,
    trans1.request_id,
    trans1.task_alloc,
    trans1.task_dealloc,
    trans2.sql_handle,
    trans2.statement_start_offset,
    trans2.statement_end_offset,
    trans2.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 trans1,
  sys.dm_exec_requests AS trans2
WHERE trans1.session_id = trans2.session_id  AND
        (trans1.request_id = trans2.request_id) and
        (trans1.task_alloc + trans1.task_dealloc > 0)
ORDER BY trans1.task_alloc DESC

No comments:

Post a Comment