找出是否存在一个或多个等待获取 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)