Original: Initial SQL Server performance issue (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