• 初涉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 

    参考文章:

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

  • 相关阅读:
    处理excel表
    生成登陆验证码
    IKAnalyzer分词例子
    JAVA中不用+ 如何实现加法
    开发者应该掌握的Java代码优化技能
    Spring Boot Maven插件
    23种设计模式(1)-单例模式
    Spring AOP 创建切面
    Spring AOP 创建增强类
    Spring AOP基础知识
  • 原文地址:https://www.cnblogs.com/woodytu/p/4543817.html
Copyright © 2020-2023  润新知