初涉SQL Server性能问题(3/4):列出阻塞的会话-阿里云开发者社区

开发者社区> 数据库> 正文

初涉SQL Server性能问题(3/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 
复制代码

本文转自Woodytu博客园博客,原文链接:http://www.cnblogs.com/woodytu/p/4543817.html,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章