Initial SQL Server performance issues (3/4): List blocked sessions

In the initial SQL Server performance issue (2/4), we discussed the list of waiting resources or running session scripts. In this article we will look at how to list blocked sessions with specific information.

1 /******************************************************************************************/2 CREATE FUNCTION [dbo]. Dba_getstatementforspid3 (  4    @spid SMALLINT  5 )  6 RETURNS NVARCHAR(4000)  7 BEGIN  8    DECLARE @SqlHandle BINARY( -)  9    DECLARE @SqlText NVARCHAR(4000)  Ten    SELECT @SqlHandle =sql_handle One        fromSys.sysprocesses with(NOLOCK)WHEREspid= @spid   A    SELECT @SqlText = [text]  from    -Sys.dm_exec_sql_text (@SqlHandle)   -    RETURN @SqlText   the END   - GO -  - /***************************************************************************************** + STEP 4:list The current blocking session information - ****************************************************************************************/ +  A SELECT at es.session_id, -Es.host_name, - db_name(database_id) asDatabaseName, -  Case  whenEs.program_name like '%sqlagent-tsql jobstep%'  Then(SELECT 'SQL AGENT JOB:'+Name fromMsdb.. SysjobsWHEREjob_id=MASTER. Dbo. Convertstringtobinary (LTRIM(RTRIM((SUBSTRING(Es.program_name,CHARINDEX('(Job', Es.program_name,0)+4, *))))))  - ELSEEs.program_nameEND   asProgram_name, - Es.login_name, inbes.session_id asblocking_session_id, -MASTER. Dbo.dba_getstatementforspid (es.session_id) as [Statement], toBes.host_name  asBlocking_hostname, +  Case  whenBes.program_name like '%sqlagent-tsql jobstep%'  Then -(SELECT 'SQL AGENT JOB:'+Name fromMsdb.. SysjobsWHEREjob_id= the MASTER. Dbo. Convertstringtobinary *(LTRIM(RTRIM((SUBSTRING(Bes.program_name,CHARINDEX('(Job', Es.program_name,0)+4, *)))))) $ ELSEBes.program_nameEND   asBlocking_program_name,Panax NotoginsengBes.login_name asBlocking_login_name, -MASTER. Dbo.dba_getstatementforspid (bes.session_id) as [Blocking Statement] the  fromsys.dm_exec_requests S + INNER JOINSys.dm_exec_sessions ES ones.session_id=s.session_id A INNER JOINSys.dm_exec_sessions BES onbes.session_id=s.blocking_session_id

This script lists the blocked and blocked statement information to help us with the problem analysis. The following script will help us list a session that has opened a transaction but is not active, that is, open a transaction, but no statements have been executed for the last 30 seconds.

1 /*****************************************************************************************2 STEP 4:list The Open session with transaction which are not active3 ****************************************************************************************/4 SELECTes.session_id,5 Es.login_name,6Es.host_name, 7 db_name(sp.dbid) asDatabaseName,8 Sp.lastwaittype,9Est.TEXT, Cn.last_read,Ten Cn.last_write, One  Case  whenEs.program_name like '%sqlagent-tsql jobstep%'  Then(SELECT 'SQL AGENT JOB:'+Name fromMsdb.. SysjobsWHEREjob_id=MASTER. Dbo. Convertstringtobinary (LTRIM(RTRIM((SUBSTRING(Es.program_name,CHARINDEX('(Job', Es.program_name,0)+4, *))))) A)ELSEEs.program_nameEND   asprogram_name -  fromsys.dm_exec_sessions es - INNER JOINSys.dm_tran_session_transactions St ones.session_id=st.session_idINNER JOINSys.dm_exec_connections cn ones.session_id=cn.session_id the INNER JOINSys.sysprocesses SP onSp.spid=es.session_id -  Left OUTER JOINSys.dm_exec_requests ER onst.session_id=er.session_id -  ander.session_id is NULL              -  CrossAPPLY sys.dm_exec_sql_text (cn.most_recent_sql_handle) est + WHERE(DATEDIFF(Ss,cn.last_read,GETDATE())+DATEDIFF(Ss,cn.last_write,GETDATE()))> - -  andLastwaittype not inch('broker_receive_waitfor','WAITFOR')                                   + GO 

Initial SQL Server performance issues (3/4): List blocked sessions