数据库动态管理视图DMV(4)

--查询tempdb中是否存在一个或多个正在获取页面锁的等待
SELECT session_id,wait_duration_ms,resource_description FROM SYS.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGE%LATCH_%' AND resource_description LIKE '2:%'

--查询tempdb中引发最多页面分配和回收操作的执行查询
SELECT TOP 10
 T1.session_id,
 T1.request_id,
 T1.TASK_ALLOC,
 T1.TASK_DEALLOC,
 T2.plan_handle,
 (
  SELECT SUBSTRING([TEXT],T2.statement_start_offset/2+1,
    (CASE WHEN statement_end_offset=-1 THEN LEN(CONVERT(NVARCHAR(MAX),[TEXT]))*2
     ELSE statement_end_offset END - T2.statement_start_offset)/2)
  FROM SYS.dm_exec_sql_text(sql_handle) AS SQL_TEXT   
 )
FROM
(
 SELECT
  session_id,
  request_id,
  SUM(internal_objects_alloc_page_count+user_objects_alloc_page_count) AS TASK_ALLOC,
  SUM(internal_objects_dealloc_page_count+user_objects_dealloc_page_count) AS TASK_DEALLOC
 FROM SYS.dm_db_task_space_usage
 GROUP BY session_id, request_id
)AS T1
JOIN SYS.dm_exec_requests AS T2 ON T1.session_id=T2.session_id AND T1.request_id=T2.request_id
WHERE T1.session_id>50
ORDER BY T1.TASK_ALLOC DESC