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 SMALLINT5 )  6RETURNS NVARCHAR (4000)  7 BEGIN8DECLARE @SqlHandle BINARY ( -)  9DECLARE @SqlText NVARCHAR (4000)  TenSELECT @SqlHandle =sql_handle OneFrom sys.sysprocesses with (nolock) WHERE spid =@spid ASELECT @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) as DatabaseName, -Case if 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, *))))))  - ELSE es.program_name END as Program_name, - Es.login_name, in bes.session_id as blocking_session_id, - MASTER. Dbo.dba_getstatementforspid (es.session_id) as [Statement], to BES. Host_name as Blocking_hostname, +Case if bes.program_name like'%sqlagent-tsql jobstep%' Then -(SELECT'SQL AGENT JOB:'+name from msdb. Sysjobs WHERE job_id= the MASTER. Dbo. Convertstringtobinary *(LTRIM (RTRIM (SUBSTRING (Bes.program_name,charindex ('(Job', Es.program_name,0)+4, *)))))) $ ELSE bes.program_name END as Blocking_program_name,Panax Notoginseng Bes.login_name as Blocking_login_name, - MASTER. Dbo.dba_getstatementforspid (bes.session_id) as [Blocking Statement] the From sys.dm_exec_requests S +INNER JOIN sys.dm_exec_sessions es on es.session_id=s.session_id AINNER JOIN sys.dm_exec_sessions BES on bes.session_id=s.blocking_session_id

View Code

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 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,Ten Cn.last_write, OneCase if 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, *))))) A ) 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_conn Ections cn on es.session_id =cn.session_id theINNER 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 ())) > - -and Lastwaittype not in ('broker_receive_waitfor','WAITFOR')                                   +GO

View Code

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