Tuesday, July 9, 2013

0 Find the top 10 Query that use the tempdb space

Below query give you the top 10 query that use the tempdb space and show the SQL text and the query plan.

 

SELECT TOP 10
        ddtsu.session_id,(ddtsu.user_objects_alloc_page_count + ddtsu.internal_objects_alloc_page_count) objs_alloc_page,
        TEXT,
        query_plan
FROM    sys.dm_db_task_space_usage ddtsu ,sys.dm_exec_requests der 
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
        CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
WHERE   ddtsu.session_id = der.session_id
AND ddtsu.session_id > 50
ORDER BY objs_alloc_page DESC ;

 

image

Double click the xml column in SSMS, it will display the query plan .

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates