s.program_name,s.host_process_id,s.client_version,
s.client_interface_name,s.login_name,s.last_request_start_time,
s.last_request_end_time,c.connect_time,c.net_transport,
c.net_packet_size,c.client_net_address,r.request_id,r.start_time,
s.status,r.command,r.database_id,r.user_id,r.blocking_session_id,
r.wait_type,r.wait_time,r.last_wait_type,r.wait_resource,
r.open_transaction_count,r.transaction_id,r.percent_complete,
r.cpu_time,r.reads,r.writes,r.granted_query_memory
FROM sys.dm_exec_requests r
RIGHT OUTER JOIN sys.dm_exec_sessions s
ON r.session_id=s.session_id
RIGHT OUTER JOIN sys.dm_exec_connections c
ON s.session_id=c.session_id
WHERE s.is_user_process=1;
或者
select * from sys.dm_os_wait_stats;
解释:wait_type 等待类型
LCK_M_SCH_S
LCK_M_SCH_M
LCK_M_S
LCK_M_U --正在等待获取更新锁
LCK_M_X --正在等待获取排他锁
LCK_M_IS
LCK_M_IU
LCK_M_IX
LCK_M_SIU
LCK_M_SIX
LCK_M_UIX
LCK_M_BU
LCK_M_RS_S
LCK_M_RS_U
LCK_M_RIn_NL --null 锁
LCK_M_RIn_S
LCK_M_RIn_U
LCK_M_RIn_X
LCK_M_RX_S
LCK_M_RX_U
LCK_M_RX_X
PAGELATCH_NL -- 内存中的锁
PAGELATCH_KP
PAGELATCH_SH
PAGELATCH_UP
PAGELATCH_EX
PAGELATCH_DT
PAGEIOLATCH_NL -- IO 表示 内存与磁盘读写 null
PAGEIOLATCH_KP -- keep
PAGEIOLATCH_SH -- share 由磁盘向内存读入数据 一般是内存瓶颈
PAGEIOLATCH_UP -- update
PAGEIOLATCH_EX -- 排他 由内存写数据到磁盘 一般是磁盘瓶颈
PAGEIOLATCH_DT -- destory
WRITELOG -- 日志