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_usage

sys.dm_db_file_space_usage

sys.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