几个有用的DMV查询

找出是否存在一个或多个等待获取 tempdb 中页面锁存器的线程

SELECT session_id,wait_type, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGE%LATCH_%' AND resource_description LIKE '2:%'

 

下面DMV查询来确认 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 query_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,
      sys.dm_exec_requests AS t2
WHERE t1.session_id = t2.session_id AND
      (t1.request_id = t2.request_id) AND t2.session_id > 50
ORDER BY t1.task_alloc DESC

 

下面DMV查询找出当前执行事务或当前执行事务等待的锁

SELECT request_session_id AS spid, resource_type AS rt,resource_database_id AS rdb,
      (CASE resource_type
            WHEN 'OBJECT' THENobject_name(resource_associated_entity_id)
            WHEN 'DATABASE' THEN ' '
            ELSE (SELECT object_name(object_id) FROM sys.partitionsWHERE hobt_id=resource_associated_entity_id)
      END) AS objname,
      resource_description as rd,
      request_mode as rm,
      request_status AS rs
FROM sys.dm_tran_locks

 

下面查询得到正在被阻塞的查询

--request_mode表示查询要请求的锁，resource_description中的mode=x表示该查询被阻塞的锁
SELECT
      t1.resource_type,
      'database' = DB_NAME(resource_database_id),
      'blk object' = t1.resource_associated_entity_id,
      t1.request_mode,
      t1.request_session_id,
      t2.blocking_session_id,
      t2.wait_duration_ms,
      (SELECT SUBSTRING(text, t3.statement_start_offset/2+1,
            (CASE WHEN t3.statement_end_offset = -1 THENLEN(CONVERT(nvarchar(max), text)) * 2
            ELSE t3.statement_end_offset END -t3.statement_end_offset)/2)
      FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
      t2.resource_description
FROM sys.dm_tran_locks AS t1, sys.dm_os_waiting_tasks AS t2,sys.dm_exec_requests AS t3
WHERE t1.lock_owner_address = t2.resource_address AND
      t1.request_request_id = t3.request_id AND
      t2.session_id = t3.session_id

 

下面查询返回表的索引被使用的情况

SELECT index_id, range_scan_count, row_lock_count, page_lock_count
FROM sys.dm_db_index_operational_stats(DB_ID('<db name>'),OBJECT_ID('table name'), NULL, NULL)