Pages

Monday, 24 September 2012

SQL Server: Part 3 : Approaching Database Server Performance Issue

In the last post, we have discussed the script to list the sessions which are waiting for resource or currently running. In this post let us see how to list the blocking sessions with required information.  

******************************************************************************************/
CREATE FUNCTION [dbo].dba_GetStatementForSpid
(  
   @spid SMALLINT  
)  
RETURNS NVARCHAR(4000)  
BEGIN  
   DECLARE @SqlHandle BINARY(20)  
   DECLARE @SqlText NVARCHAR(4000)  
   SELECT @SqlHandle = sql_handle   
      FROM sys.sysprocesses WITH (nolock) WHERE   spid = @spid  
   SELECT @SqlText = [text] FROM   
      sys.dm_exec_sql_text(@SqlHandle)  
   RETURN @SqlText  
END  
GO

/*****************************************************************************************
STEP 4: List the current blocking session information
****************************************************************************************/


SELECT

es.session_id,
es.HOST_NAME,
DB_NAME(database_id) AS DatabaseName
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)))))
ELSE es.program_name END  AS program_name ,
es.login_name ,
bes.session_id AS Blocking_session_id,
MASTER.DBO.dba_GetStatementForSpid(es.session_id) AS [Statement],
bes.HOST_NAME AS Blocking_hostname,
CASE WHEN Bes.program_name LIKE '%SQLAgent - TSQL JobStep%' THEN
(SELECT 'SQL AGENT JOB: '+name FROM msdb..sysjobs WHERE job_id=
MASTER.DBO.ConvertStringToBinary 
(LTRIM(RTRIM((SUBSTRING(Bes.program_name,CHARINDEX('(job',es.program_name,0)+4,35))))))
ELSE Bes.program_name END  AS Blocking_program_name,
bes.login_name AS Blocking_login_name,
  MASTER.DBO.dba_GetStatementForSpid(bes.session_id ) AS [Blocking Statement]
FROM sys.dm_exec_requests S 
INNER JOIN sys.dm_exec_sessions  es ON es.session_id=s.session_id
INNER JOIN sys.dm_exec_sessions  bes ON bes.session_id=s.blocking_session_id


This script will list the blocked and blocking statement information and will be helpful to troubleshoot. Below script will help us to identify the sessions which have an open transaction but not active . That is, a sessions with an open transaction but not running any statement in the last 30 seconds.

/*****************************************************************************************
STEP 4: List the Open session with transaction which is not active
****************************************************************************************/

SELECT es.session_id
es.login_name
es.HOST_NAME
DB_NAME(SP.dbid) AS DatabaseName,
sp.lastwaittype,
est.TEXT,cn.last_read
cn.last_write,
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)))))
)
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_connections cn ON es.session_id = cn.session_id 
INNER 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()))>30
AND lastwaittype NOT IN ('BROKER_RECEIVE_WAITFOR' ,'WAITFOR')                                  GO


If you liked this post, do like my page on FaceBook

10 comments:

  1. [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] نشتري الاثاث المستعمل بجدة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] نشتري الاثاث المستعمل بمكة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] شراء الاثاث المستعمل بجدة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] شراء الاثاث المستعمل بمكة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] ارقام شراء الاثاث المستعمل بجدة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] ارقام شراء الاثاث المستعمل بمكة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] ارقام محلات شراء الاثاث المستعمل بجدة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] شراء اثاث مستعمل بجدة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] شراء اثاث مستعمل جدة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] شركات شراء اثاث مستعمل في جدة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] شراء الاثاث المستعمل بمكة المكرمة [/URL]

    [URL="http://fcnsc.net/p651//"]محلات الاثاث المستعمل بالمدينة المنورة[/URL]
    [URL="http://fcnsc.net/p651//"]ارقام الاثاث المستعمل بالمدينة المنورة [/URL]
    [URL="http://fcnsc.net/p651//"]حقين الاثاث المستعمل بالمدينة المنورة [/URL]
    [URL="http://fcnsc.net/p651//"]شراء الاثاث المستعمل بالمدينة المنورة [/URL]
    [URL="http://fcnsc.net/p651//"]شراء اثاث مستعمل بالمدينة المنورة[/URL]

    ReplyDelete
  2. [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] نشتري الاثاث المستعمل بجدة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] نشتري الاثاث المستعمل بمكة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] شراء الاثاث المستعمل بجدة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] شراء الاثاث المستعمل بمكة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] ارقام شراء الاثاث المستعمل بجدة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] ارقام شراء الاثاث المستعمل بمكة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] ارقام محلات شراء الاثاث المستعمل بجدة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] شراء اثاث مستعمل بجدة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] شراء اثاث مستعمل جدة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] شركات شراء اثاث مستعمل في جدة [/URL]
    [URL="http://fcnsc.com/used-furniture-in-jed-and-mec//"] شراء الاثاث المستعمل بمكة المكرمة [/URL]

    [URL="http://fcnsc.net/p651//"]محلات الاثاث المستعمل بالمدينة المنورة[/URL]
    [URL="http://fcnsc.net/p651//"]ارقام الاثاث المستعمل بالمدينة المنورة [/URL]
    [URL="http://fcnsc.net/p651//"]حقين الاثاث المستعمل بالمدينة المنورة [/URL]
    [URL="http://fcnsc.net/p651//"]شراء الاثاث المستعمل بالمدينة المنورة [/URL]
    [URL="http://fcnsc.net/p651//"]شراء اثاث مستعمل بالمدينة المنورة[/URL]

    ReplyDelete



  3. I loved the way you discuss the topic great work thanks for the share, Let me share this, Hadoop training in pune

    ReplyDelete
  4. Nice Blog.Thank you for Sharing. We are leading erp software software solution providers in chennai. For more details call +91 9677025199.
    erp software in chennai | erp providers in chennai | online events registration

    ReplyDelete
  5. https://kdp.amazon.com/community/profile.jspa?editMode=true&userID=1424531
    https://kdp.amazon.com/community/profile.jspa?editMode=true&userID=1427388
    https://kdp.amazon.com/community/profile.jspa?editMode=true&userID=1428236
    https://tmblr.co/ZcaPoj2Bk7Kpf
    https://tmblr.co/ZcaPoj2Bk7RDQ

    ReplyDelete
  6. https://tmblr.co/ZcaPoj2Bk7ZaB
    https://tmblr.co/ZcaPoj2BkA5WZ
    https://tmblr.co/ZcaPoj2BkBsuV
    https://tmblr.co/ZcaPoj2BkKQCs
    https://java.net/people/1214494-khairyayman
    https://java.net/people/1214497-radwaayman

    ReplyDelete
  7. https://challenges.openideo.com/profiles/1119874714489381863241486229946090
    http://www.dead.net/member/khairyayman
    https://vimeo.com/user54212503
    https://mootools.net/forge/profile/naklafshdmam
    http://bionumbers.hms.harvard.edu/bionumber.aspx?&id=113190

    ReplyDelete