tempdb 경합 모니터링

SELECT

   session_id,

   wait_type,

   wait_duration_ms,

   blocking_session_id,

   resource_description,

   ResourceType = CASE

   WHEN PageID = 1 OR PageID % 8088 = 0 THEN 'Is PFS Page'

   WHEN PageID = 2 OR PageID % 511232 = 0 THEN 'Is GAM Page'

   WHEN PageID = 3 OR (PageID - 1) % 511232 = 0 THEN 'Is SGAM Page'

       ELSE 'Is Not PFS, GAM, or SGAM page'

   END

FROM (  SELECT 

           session_id,

           wait_type,

           wait_duration_ms,

           blocking_session_id,

           resource_description,

           CAST(RIGHT(resource_description , LEN (resource_description)

           - CHARINDEX (':', resource_description, 3)) AS INT ) AS PageID

       FROM sys .dm_os_waiting_tasks

       WHERE wait_type LIKE 'PAGE%LATCH_%'

         AND resource_description LIKE '2:%'

) AS tab;

 

 

처리하지 못하고 대기중인 쿼리 조회

 

SELECT

[owt].[session_id],

[owt].[exec_context_id],

[owt].[wait_duration_ms],

[owt].[wait_type],

[owt].[blocking_session_id],

[owt].[resource_description],

CASE [owt].[wait_type]

WHEN N'CXPACKET' THEN

RIGHT ([owt].[resource_description],

CHARINDEX (N'=', REVERSE ([owt].[resource_description])) - 1)

ELSE NULL

END AS [Node ID],

[es].[program_name],

[est].text,

[er].[database_id],

[eqp].[query_plan],

[er].[cpu_time]

FROM sys.dm_os_waiting_tasks [owt]

INNER JOIN sys.dm_exec_sessions [es] ON

[owt].[session_id] = [es].[session_id]

INNER JOIN sys.dm_exec_requests [er] ON

[es].[session_id] = [er].[session_id]

OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]

OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]

WHERE

[es].[is_user_process] = 1

ORDER BY

[owt].[session_id],

[owt].[exec_context_id];

GO

 

https://sungwookkang.com/834