初涉SQL Server性能问题（3/4）：列出阻塞的会话

在 初涉SQL Server性能问题（2/4）里，我们讨论了列出等待资源或正运行的会话脚本。这篇文章我们会看看如何列出包含具体信息的话阻塞会话清单。

/******************************************************************************************/
CREATE FUNCTION [dbo].dba_GetStatementForSpid
(  
   @spid SMALLINT  
)  
RETURNS NVARCHAR(4000)  
BEGIN  
   DECLARE @SqlHandle BINARY(20)  
   DECLARE @SqlText NVARCHAR(4000)  
   SELECT @SqlHandle = sql_handle   
      FROM sys.sysprocesses WITH (nolock) WHERE   spid = @spid  
   SELECT @SqlText = [text] FROM   
      sys.dm_exec_sql_text(@SqlHandle)  
   RETURN @SqlText  
END  
GO

/*****************************************************************************************
STEP 4: List the current blocking session information
****************************************************************************************/

SELECT
es.session_id,
es.HOST_NAME,
DB_NAME(database_id) AS DatabaseName, 
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)))))) 
ELSE es.program_name END  AS program_name ,
es.login_name ,
bes.session_id AS Blocking_session_id,
MASTER.DBO.dba_GetStatementForSpid(es.session_id) AS [Statement],
bes.HOST_NAME AS Blocking_hostname,
CASE WHEN Bes.program_name LIKE '%SQLAgent - TSQL JobStep%' THEN
(SELECT 'SQL AGENT JOB: '+name FROM msdb..sysjobs WHERE job_id=
MASTER.DBO.ConvertStringToBinary 
(LTRIM(RTRIM((SUBSTRING(Bes.program_name,CHARINDEX('(job',es.program_name,0)+4,35))))))
ELSE Bes.program_name END  AS Blocking_program_name,
bes.login_name AS Blocking_login_name,
  MASTER.DBO.dba_GetStatementForSpid(bes.session_id ) AS [Blocking Statement]
FROM sys.dm_exec_requests S 
INNER JOIN sys.dm_exec_sessions  es ON es.session_id=s.session_id
INNER JOIN sys.dm_exec_sessions  bes ON bes.session_id=s.blocking_session_id

这个脚本会列出被阻塞和正阻塞的语句信息，帮助我们进行问题分析。下面的脚本会帮助我们列出已经打开事务但未活动的会话，即打开事务，但上30秒内都没执行任何语句的会话。

/*****************************************************************************************
STEP 4: List the Open session with transaction which is not active
****************************************************************************************/
SELECT es.session_id, 
es.login_name, 
es.HOST_NAME, 
DB_NAME(SP.dbid) AS DatabaseName,
sp.lastwaittype,
est.TEXT,cn.last_read, 
cn.last_write, 
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)))))
)ELSE es.program_name END  AS program_name 
FROM sys.dm_exec_sessions es
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 
INNER JOIN sys.sysprocesses SP ON SP.spid=es.session_id                 
LEFT OUTER JOIN sys.dm_exec_requests er  ON st.session_id = er.session_id   
AND er.session_id IS NULL             
CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) est                
WHERE (DATEDIFF(SS,cn.last_read,GETDATE())+DATEDIFF(SS,cn.last_write,GETDATE()))>30
AND lastwaittype NOT IN ('BROKER_RECEIVE_WAITFOR' ,'WAITFOR')                                  
GO 

参考文章：

http://www.sqlservercentral.com/blogs/practicalsqldba/2012/09/24/sql-server-part-3-approaching-database-server-performance-issue-/