SELECT a.session_id,
a.wait_type,
a.wait_duration_ms,
a.blocking_session_id,
a.resource_description,
CASE
WHEN CAST(RIGHT(a.resource_description, LEN(a.resource_description) - CHARINDEX(':', a.resource_description, 3)) AS INT)
- 1 % 8088 = 0 THEN 'Is PFS Page'
WHEN CAST(RIGHT(a.resource_description,
LEN(a.resource_description)
- CHARINDEX(':', a.resource_description, 3)) AS INT)
- 2 % 511232 = 0 THEN 'Is GAM Page'
WHEN CAST(RIGHT(a.resource_description,
LEN(a.resource_description)
- CHARINDEX(':', a.resource_description, 3)) AS INT)
- 3 % 511232 = 0 THEN 'Is SGAM Page'
ELSE 'Is Not PFS, GAM, or SGAM page'
END resourcetype,
c.text AS SQLText
FROM sys.dm_os_waiting_tasks a
INNER JOIN sys.sysprocesses b
ON
a.session_id = b.spid
OUTER APPLY sys.dm_exec_sql_text(b.sql_handle) c
WHERE a.wait_type LIKE 'PAGE%LATCH_%'
AND a.resource_description LIKE '2:%';
https://sqlmonitormetrics.red-gate.com/tempdb-allocation-contention/
'SQL SERVER' 카테고리의 다른 글
| SQL Server 엔지니에어게 필요한 오라클 Linked Server의 모든것 (0) | 2019.09.24 |
|---|---|
| Oracle Linked Server Fetch Size 설정 (0) | 2019.09.03 |
| 새로운 서버로 Data 이관(How to migrate SQL Server from one machine to another) (0) | 2019.06.17 |
| index에 포함된 include column 조회하기 (0) | 2019.05.23 |
| Extended Event Data Query로 Select (0) | 2019.02.19 |