SELECT
wait_type ,
wait_time_ms / 1000. AS wait_time_s ,
100. * wait_time_ms / SUM ( wait_time_ms ) OVER () AS pct ,
ROW_NUMBER () OVER ( ORDER BY wait_time_ms DESC ) AS rn
FROM sys . dm_os_wait_stats
WHERE wait_type
NOT IN
( 'CLR_SEMAPHORE' , 'LAZYWRITER_SLEEP' , 'LAZYWRITER_SLEEP' , 'RESOURCE_QUEUE' , 'SLEEP_TASK' , 'SLEEP_SYSTEMTASK' , 'SQLTRACE_BUFFER_FLUSH' ,
'WAITFOR' , 'LOGMGR_QUEUE' , 'CHECKPOINT_QUEUE' , 'REQUEST_FOR_DEADLOCK_SEARCH' , 'XE_TIME_EVENT' , 'BROKER_TO_FLUSH' , 'BROKER_TASK_STOP' ,
'CLR_MANUAL_EVENT'
,
'DISPATCHER_QUEUE,SEMAPHORE'
,
'FT_LFTS_SCHEDULER_IDLE_WAIT'
,
'XE_DISPATCHER_WAIT'
,
'XE_DISPATCHER_JOIN'
,
'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
,
'SLEEP_TASK'
,
'SLEEP_SYSTEMTASK'
,
'SQLTRACE_BUFFER_FLUSH'
,
'WAITFOR'
)
sys.dm_db_session_space_usagesys.dm_db_file_space_usagesys.dm_db_task_space_usage 可以查看会话对于tempdb的使用情况
select db_name(database_id) as 'Database Name', file_id, io_stall_read_ms/num_of_reads 'Avg Read by ms', io_stall_write_ms/num_of_writes 'Avg write by ms', num_of_reads, num_of_writes from sys.dm_io_virtual_file_stats(5, 1)where num_of_reads > 0 and num_of_writes > 0
查看数据文件自上次启动以来的的读写速度
select * from sys.dm_os_waiting_tasks where wait_type like 'LCK%';此语句可以定位获取数据库中的阻塞, 视图中有涉及的等待的session id, 等待的资源类型。以下截图展示的是更新时session id是52的会话阻塞了另一个会话，比较重要的是这个blocking_session_id与resource_description。可以根据resource_description中的fileid, dbid, pageid等信息定位到阻塞时涉及的对象。
查看数据库中的锁select * from sys.dm_tran_locks
