原文:
初涉SQL Server性能问题（3/4）：列出阻塞的会话
在 初涉SQL Server性能问题（2/4）里，我们讨论了列出等待资源或正运行的会话脚本。这篇文章我们会看看如何列出包含具体信息的话阻塞会话清单。
1 /******************************************************************************************/ 2 CREATE FUNCTION [dbo].dba_GetStatementForSpid 3 ( 4 @spid SMALLINT 5 ) 6 RETURNS NVARCHAR(4000) 7 BEGIN 8 DECLARE @SqlHandle BINARY(20) 9 DECLARE @SqlText NVARCHAR(4000) 10 SELECT @SqlHandle = sql_handle 11 FROM sys.sysprocesses WITH (nolock) WHERE spid = @spid 12 SELECT @SqlText = [text] FROM 13 sys.dm_exec_sql_text(@SqlHandle) 14 RETURN @SqlText 15 END 16 GO 17 18 /***************************************************************************************** 19 STEP 4: List the current blocking session information 20 ****************************************************************************************/ 21 22 SELECT 23 es.session_id, 24 es.HOST_NAME, 25 DB_NAME(database_id) AS DatabaseName, 26 CASE WHEN es.program_name LIKE '%SQLAgent - TSQL JobStep%' THEN (SELECT 'SQL AGENT JOB: '+name FROM msdb..sysjobs WHERE job_id=MASTER.DBO.ConvertStringToBinary (LTRIM(RTRIM((SUBSTRING(es.program_name,CHARINDEX('(job',es.program_name,0)+4,35)))))) 27 ELSE es.program_name END AS program_name , 28 es.login_name , 29 bes.session_id AS Blocking_session_id, 30 MASTER.DBO.dba_GetStatementForSpid(es.session_id) AS [Statement], 31 bes.HOST_NAME AS Blocking_hostname, 32 CASE WHEN Bes.program_name LIKE '%SQLAgent - TSQL JobStep%' THEN 33 (SELECT 'SQL AGENT JOB: '+name FROM msdb..sysjobs WHERE job_id= 34 MASTER.DBO.ConvertStringToBinary 35 (LTRIM(RTRIM((SUBSTRING(Bes.program_name,CHARINDEX('(job',es.program_name,0)+4,35)))))) 36 ELSE Bes.program_name END AS Blocking_program_name, 37 bes.login_name AS Blocking_login_name, 38 MASTER.DBO.dba_GetStatementForSpid(bes.session_id ) AS [Blocking Statement] 39 FROM sys.dm_exec_requests S 40 INNER JOIN sys.dm_exec_sessions es ON es.session_id=s.session_id 41 INNER JOIN sys.dm_exec_sessions bes ON bes.session_id=s.blocking_session_id
这个脚本会列出被阻塞和正阻塞的语句信息，帮助我们进行问题分析。下面的脚本会帮助我们列出已经打开事务但未活动的会话，即打开事务，但上30秒内都没执行任何语句的会话。
1 /***************************************************************************************** 2 STEP 4: List the Open session with transaction which is not active 3 ****************************************************************************************/ 4 SELECT es.session_id, 5 es.login_name, 6 es.HOST_NAME, 7 DB_NAME(SP.dbid) AS DatabaseName, 8 sp.lastwaittype, 9 est.TEXT,cn.last_read, 10 cn.last_write, 11 CASE WHEN es.program_name LIKE '%SQLAgent - TSQL JobStep%' THEN(SELECT 'SQL AGENT JOB: '+name FROM msdb..sysjobs WHERE job_id=MASTER.DBO.ConvertStringToBinary (LTRIM(RTRIM((SUBSTRING(es.program_name,CHARINDEX('(job',es.program_name,0)+4,35))))) 12 )ELSE es.program_name END AS program_name 13 FROM sys.dm_exec_sessions es 14 INNER JOIN sys.dm_tran_session_transactions st ON es.session_id = st.session_id INNER JOIN sys.dm_exec_connections cn ON es.session_id = cn.session_id 15 INNER JOIN sys.sysprocesses SP ON SP.spid=es.session_id 16 LEFT OUTER JOIN sys.dm_exec_requests er ON st.session_id = er.session_id 17 AND er.session_id IS NULL 18 CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) est 19 WHERE (DATEDIFF(SS,cn.last_read,GETDATE())+DATEDIFF(SS,cn.last_write,GETDATE()))>30 20 AND lastwaittype NOT IN ('BROKER_RECEIVE_WAITFOR' ,'WAITFOR') 21 GO
