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

Source: Internet
Author: User
Tags getdate rtrim sessions

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.