запрос кто в данный момент использует tempdb
SELECT st.dbid AS QueryExecutionContextDBID, DB_NAME(st.dbid) AS QueryExecContextDBNAME, st.objectid AS ModuleObjectId, SUBSTRING(st.TEXT, dmv_er.statement_start_offset/2 + 1, (CASE WHEN dmv_er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2 ELSE dmv_er.statement_end_offset END - dmv_er.statement_start_offset)/2) AS Query_Text, dmv_tsu.session_id , dmv_tsu.request_id, dmv_tsu.exec_context_id, (dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts, (dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts, dmv_er.start_time, dmv_er.command, dmv_er.open_transaction_count, dmv_er.percent_complete, dmv_er.estimated_completion_time, dmv_er.cpu_time, dmv_er.total_elapsed_time, dmv_er.reads,dmv_er.writes, dmv_er.logical_reads, dmv_er.granted_query_memory, dmv_es.HOST_NAME, dmv_es.login_name, dmv_es.program_name FROM sys.dm_db_task_space_usage dmv_tsu INNER JOIN sys.dm_exec_requests dmv_er ON (dmv_tsu.session_id = dmv_er.session_id AND dmv_tsu.request_id = dmv_er.request_id) INNER JOIN sys.dm_exec_sessions dmv_es ON (dmv_tsu.session_id = dmv_es.session_id) CROSS APPLY sys.dm_exec_sql_text(dmv_er.sql_handle) st WHERE (dmv_tsu.internal_objects_alloc_page_count + dmv_tsu.user_objects_alloc_page_count) > 0 ORDER BY (dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) + (dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) DESC
Скрипт показывает сколько в мегабайтах занимает место в tempdb запрос
SELECT TS.session_id ,
TS.request_id ,
TS.database_id ,
CAST(TS.user_objects_alloc_page_count / 128 AS DECIMAL(15, 2)) [Total Allocation User Objects MB] ,
CAST(( TS.user_objects_alloc_page_count
- TS.user_objects_dealloc_page_count ) / 128 AS DECIMAL(15, 2)) [Net Allocation User Objects MB] ,
CAST(TS.internal_objects_alloc_page_count / 128 AS DECIMAL(15, 2)) [Total Allocation Internal Objects MB] ,
CAST(( TS.internal_objects_alloc_page_count
- TS.internal_objects_dealloc_page_count ) / 128 AS DECIMAL(15,
2)) [Net Allocation Internal Objects MB] ,
CAST(( TS.user_objects_alloc_page_count
+ internal_objects_alloc_page_count ) / 128 AS DECIMAL(15, 2)) [Total Allocation MB] ,
CAST(( TS.user_objects_alloc_page_count
+ TS.internal_objects_alloc_page_count
- TS.internal_objects_dealloc_page_count
- TS.user_objects_dealloc_page_count ) / 128 AS DECIMAL(15, 2)) [Net Allocation MB] ,
T.text [Query Text]
FROM sys.dm_db_task_space_usage TS
INNER JOIN sys.dm_exec_requests ER ON ER.request_id = TS.request_id
AND ER.session_id = TS.session_id
OUTER APPLY sys.dm_exec_sql_text(ER.sql_handle) T
order by [Total Allocation MB] desc
получить использование пространства сессией, включая текущие запросы.
SELECT COALESCE(T1.session_id, T2.session_id) [session_id] , T1.request_id ,
COALESCE(T1.database_id, T2.database_id) [database_id],
COALESCE(T1.[Total Allocation User Objects], 0)
+ T2.[Total Allocation User Objects] [Total Allocation User Objects] ,
COALESCE(T1.[Net Allocation User Objects], 0)
+ T2.[Net Allocation User Objects] [Net Allocation User Objects] ,
COALESCE(T1.[Total Allocation Internal Objects], 0)
+ T2.[Total Allocation Internal Objects] [Total Allocation Internal Objects] ,
COALESCE(T1.[Net Allocation Internal Objects], 0)
+ T2.[Net Allocation Internal Objects] [Net Allocation Internal Objects] ,
COALESCE(T1.[Total Allocation], 0) + T2.[Total Allocation] [Total Allocation] ,
COALESCE(T1.[Net Allocation], 0) + T2.[Net Allocation] [Net Allocation] ,
COALESCE(T1.[Query Text], T2.[Query Text]) [Query Text]
FROM ( SELECT TS.session_id ,
TS.request_id ,
TS.database_id ,
CAST(TS.user_objects_alloc_page_count / 128 AS DECIMAL(15,
2)) [Total Allocation User Objects] ,
CAST(( TS.user_objects_alloc_page_count
- TS.user_objects_dealloc_page_count ) / 128 AS DECIMAL(15,
2)) [Net Allocation User Objects] ,
CAST(TS.internal_objects_alloc_page_count / 128 AS DECIMAL(15,
2)) [Total Allocation Internal Objects] ,
CAST(( TS.internal_objects_alloc_page_count
- TS.internal_objects_dealloc_page_count ) / 128 AS DECIMAL(15,
2)) [Net Allocation Internal Objects] ,
CAST(( TS.user_objects_alloc_page_count
+ internal_objects_alloc_page_count ) / 128 AS DECIMAL(15,
2)) [Total Allocation] ,
CAST(( TS.user_objects_alloc_page_count
+ TS.internal_objects_alloc_page_count
- TS.internal_objects_dealloc_page_count
- TS.user_objects_dealloc_page_count ) / 128 AS DECIMAL(15,
2)) [Net Allocation] ,
T.text [Query Text]
FROM sys.dm_db_task_space_usage TS
INNER JOIN sys.dm_exec_requests ER ON ER.request_id = TS.request_id
AND ER.session_id = TS.session_id
OUTER APPLY sys.dm_exec_sql_text(ER.sql_handle) T
) T1
RIGHT JOIN ( SELECT SS.session_id ,
SS.database_id ,
CAST(SS.user_objects_alloc_page_count / 128 AS DECIMAL(15,
2)) [Total Allocation User Objects] ,
CAST(( SS.user_objects_alloc_page_count
- SS.user_objects_dealloc_page_count )
/ 128 AS DECIMAL(15, 2)) [Net Allocation User Objects] ,
CAST(SS.internal_objects_alloc_page_count / 128 AS DECIMAL(15,
2)) [Total Allocation Internal Objects] ,
CAST(( SS.internal_objects_alloc_page_count
- SS.internal_objects_dealloc_page_count )
/ 128 AS DECIMAL(15, 2)) [Net Allocation Internal Objects] ,
CAST(( SS.user_objects_alloc_page_count
+ internal_objects_alloc_page_count ) / 128 AS DECIMAL(15,
2)) [Total Allocation] ,
CAST(( SS.user_objects_alloc_page_count
+ SS.internal_objects_alloc_page_count
- SS.internal_objects_dealloc_page_count
- SS.user_objects_dealloc_page_count )
/ 128 AS DECIMAL(15, 2)) [Net Allocation] ,
T.text [Query Text]
FROM sys.dm_db_session_space_usage SS
LEFT JOIN sys.dm_exec_connections CN ON CN.session_id = SS.session_id
OUTER APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) T
) T2 ON T1.session_id = T2.session_id
Круто! Спасибо