程式筆記

SELECT
    SPID                = er.session_id
		--exec sp_who SPID
		--找出哪個DB程序被Blocked
    ,STATUS             = ses.STATUS
		--參考https://dba.stackexchange.com/questions/211034/sql-server-db-status-runnable-sleeping-suspended-running-and-background-meani
		--SLEEPING:應用程式應共用一條Connection, 並設置Sleeping Counter, 定期重新連線, 使用結束應關閉Connection
    ,[Login]            = ses.login_name
    ,Host               = ses.host_name
    ,BlockBy_SessionID              = er.blocking_session_id
		--被哪個session_id block
		--exec sp_who BlockBy_SessionID
    ,DBName             = DB_Name(er.database_id)
    ,CommandType        = er.command
    ,SQLStatement       = st.text
    ,ObjectName         = OBJECT_NAME(st.objectid)
    ,ElapsedMS          = er.total_elapsed_time
		--要求到達後所經過的總時間 (以毫秒為單位)。 
    ,CPUTime            = er.cpu_time
		--要求所用的 CPU 時間 (以毫秒為單位)。
    ,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
	,open_transaction_count = ISNULL(r.open_transaction_count,0)
	,TaskState    = ISNULL(t.task_state, N'')
	,WaitDurationMS      = ISNULL(w.wait_duration_ms, 0)
	,CPUMS = ses.cpu_time 
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 OUTER JOIN sys.dm_exec_requests r ON (ses.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN 
(   SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
    FROM sys.dm_os_waiting_tasks 
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
WHERE er.blocking_session_id<>0	--正被blocking的session
	OR (r.open_transaction_count>0 and RTRIM(t.task_state) = 'sleeping') --交易狀況有問題
	OR w.wait_duration_ms>10000	--等待時間過長
	OR ses.cpu_time > 1000 --CPU 使用量過大