Search

Showing posts with label TempDB Grows. Show all posts
Showing posts with label TempDB Grows. Show all posts

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