前些天一个棘手的问题,早上上班刚到公司不久,就发现系统里面出现了大量的查询超时。
SELECT
SPID = er.session_id
,STATUS = ses.STATUS
,[LOGIN] = ses.login_name
,HOST = ses.host_name
,BlkBy = er.blocking_session_id
,DBName = DB_NAME(er.database_id)
,CommandType = er.command
,SQLStatement = st.text
,BlockingText = bst.text
,ObjectName = OBJECT_NAME(st.objectid)
,ElapsedMS = er.total_elapsed_time
,CPUTime = er.cpu_time
,IOReads = er.logical_reads + er.reads
,IOWrites = er.writes
,LastWaitType = er.last_wait_type
,StartTime = er.start_time
,Protocol = con.net_transport
,ConnectionWrites = con.num_writes
,ConnectionReads = con.num_reads
,ClientAddress = con.client_net_address
,Authentication = con.auth_scheme
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
LEFT JOIN sys.dm_exec_requests ber
ON er.blocking_session_id=ber.session_id
OUTER APPLY sys.dm_exec_sql_text(ber.sql_handle) bst
WHERE er.session_id > 50
ORDER BY er.blocking_session_id DESC,er.session_id
作者:章为忠
关注我的微信公众号,获取相关的 源代码及视频资料。