tempdb 많이 사용하는 실행계획과 텍스트 반환

SELECT TOP 10

TSU.session_id

, TSU.request_id

, TSU.task_alloc_cnt

, TSU.task_dealloc_cnt

, EST.text as [query_text]

, EQP.query_plan as [plan_xml]

FROM (

SELECT session_id

, request_id

, SUM(internal_objects_alloc_page_count) as [task_alloc_cnt]

, SUM(internal_objects_dealloc_page_count) as [task_dealloc_cnt]

FROM sys.dm_db_task_space_usage

GROUP BY session_id, request_id --병렬처리 그룹화

) as [TSU]

INNER JOIN sys.dm_exec_requests as [ER]

ON TSU.session_id = ER.session_id

AND TSU.request_id = ER.request_id

OUTER APPLY sys.dm_exec_sql_text (ER.sql_handle) as [EST]

OUTER APPLY sys.dm_exec_query_plan (ER.plan_handle) as [EQP]

WHERE TSU.session_id <> @@spid

ORDER BY TSU.task_alloc_cnt DESC

-- 버전스토오 사용시 TEMPDB 사용량 확인

SELECT TOP 10

SDT.session_id

, SDT.elapsed_time_seconds

, SDT.is_snapshot

, EST.text as query_text

, EQP.query_plan as plan_xml

FROM sys.dm_tran_active_snapshot_database_transactions as [SDT]

LEFT OUTER JOIN sys.dm_exec_requests as [ER]

ON SDT.session_id = ER.session_id

OUTER APPLY sys.dm_exec_sql_text (ER.sql_handle) as [EST]

OUTER APPLY sys.dm_exec_query_plan (ER.plan_handle) as [EQP]

ORDER BY elapsed_time_seconds DESC;