2

I have a stored procedure called sp_radhe that I put on my servers and it has been helping me to "see" what is happening internally.

here is the code of this stored procedure:

USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--DROP PROCEDURE dbo.sp_radhe
GO


CREATE PROCEDURE dbo.sp_radhe 
AS

/*
=======================================================================
Script  :   SP_RADHE
Author  :   Marcelo Miorelli
Date    :   04 MAR 2013 Wednesday
Desc    :   shows the current processes

Usage   :  sp_radhe 
           -- same as sp_who2       
=======================================================================
History
Date           Action           User                 Desc
-----------------------------------------------------------------------
27-oct-2014    changed          Marcelo Miorelli    commented out the line --and es.status = 'running'
                                                    so the procedure returns any es.status

=======================================================================
*/

--======================================
-- describe primary blocks of processing
--======================================

------------------------------------------------
-- describe action of logical groups of commands
------------------------------------------------

-- describe individual actions within a command set

BEGIN

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


SELECT es.session_id AS session_id
,COALESCE(es.original_login_name, 'No Info') AS login_name
,COALESCE(es.host_name,'No Info') AS hostname
,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch
,es.status
,COALESCE(er.blocking_session_id,0) AS blocked_by
,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype
,COALESCE(er.wait_time,0) AS waittime
,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype
,COALESCE(er.wait_resource,'') AS waitresource
,coalesce(db_name(er.database_id),'No Info') as dbid
,COALESCE(er.command,'AWAITING COMMAND') AS cmd
,sql_text=st.text
,transaction_isolation =
    CASE es.transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'Read Uncommitted'
        WHEN 2 THEN 'Read Committed'
        WHEN 3 THEN 'Repeatable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
    END
,COALESCE(es.cpu_time,0) 
    + COALESCE(er.cpu_time,0) AS cpu
,COALESCE(es.reads,0) 
    + COALESCE(es.writes,0) 
    + COALESCE(er.reads,0) 
    + COALESCE(er.writes,0) AS physical_io
,COALESCE(er.open_transaction_count,-1) AS open_tran
,COALESCE(es.program_name,'') AS program_name
,es.login_time
FROM sys.dm_exec_sessions es
    LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
    LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
    LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
    LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id
    LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
where es.is_user_process = 1 
  and es.session_id <> @@spid
  --and es.status = 'running'

GO

exec sys.sp_MS_marksystemobject 'sp_radhe'

GO

However, when a process is being blocked by some other process that is not active, I am struggling to figure it out the T-SQL of the blocking code.

For example:

enter image description here

the session 92 you can see on the above picture is a select and the session 75 is an update that I left the transaction open.

session 92

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT TOP 1000 [accountID]
      ,[accountCreateKeyID]
      ,[totalAccountCreditValueLocal]
      ,[accountCreateDate]
      ,[createdDate]
      ,[createdBy]
      ,[modifiedDate]
      ,[modifiedBy]
  FROM [TableBackups].[dbo].[_AO20150806_crm_build_account_DoNotDelete]
  WITH (HOLDLOCK)

session 75

  BEGIN TRANSACTION T1

  SELECT @@TRANCOUNT

  update 
  [TableBackups].[dbo].[_AO20150806_crm_build_account_DoNotDelete]
  set [totalAccountCreditValueLocal] = 1000
  where accountID = 1

How can I find the code of the blocking session, in this case, the session 75 when the session's status is 'sleeping'?

New version this new version shows also the blocking session, however, I could not find out how to get the database name and other data from a sleeping session.

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT es.session_id AS session_id
,COALESCE(es.original_login_name, 'No Info') AS login_name
,COALESCE(es.host_name,'No Info') AS hostname
,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch
,es.status
,COALESCE(er.blocking_session_id,0) AS blocked_by
,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype
,COALESCE(er.wait_time,0) AS waittime
,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype
,COALESCE(er.wait_resource,'') AS waitresource
,coalesce(db_name(er.database_id),'No Info') as dbid
,COALESCE(er.command,'AWAITING COMMAND') AS cmd
,sql_text=st.text
,transaction_isolation =
    CASE es.transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'Read Uncommitted'
        WHEN 2 THEN 'Read Committed'
        WHEN 3 THEN 'Repeatable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
    END
,COALESCE(es.cpu_time,0) 
    + COALESCE(er.cpu_time,0) AS cpu
,COALESCE(es.reads,0) 
    + COALESCE(es.writes,0) 
    + COALESCE(er.reads,0) 
    + COALESCE(er.writes,0) AS physical_io
,COALESCE(er.open_transaction_count,-1) AS open_tran
,COALESCE(es.program_name,'') AS program_name
,es.login_time
FROM sys.dm_exec_sessions es
    LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
    LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
    LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
    LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id
    LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st

where es.is_user_process = 1 
  and es.session_id <> @@spid

UNION 

SELECT es.session_id AS session_id
,COALESCE(es.original_login_name, 'No Info') AS login_name
,COALESCE(es.host_name,'No Info') AS hostname
,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch
,es.status
,COALESCE(er.blocking_session_id,0) AS blocked_by
,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype
,COALESCE(er.wait_time,0) AS waittime
,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype
,COALESCE(er.wait_resource,'') AS waitresource
,coalesce(db_name(er.database_id),'No Info') as dbid
,COALESCE(er.command,'AWAITING COMMAND') AS cmd
,sql_text=st.text
,transaction_isolation =
    CASE es.transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'Read Uncommitted'
        WHEN 2 THEN 'Read Committed'
        WHEN 3 THEN 'Repeatable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
    END
,COALESCE(es.cpu_time,0) 
    + COALESCE(er.cpu_time,0) AS cpu
,COALESCE(es.reads,0) 
    + COALESCE(es.writes,0) 
    + COALESCE(er.reads,0) 
    + COALESCE(er.writes,0) AS physical_io
,COALESCE(er.open_transaction_count,-1) AS open_tran
,COALESCE(es.program_name,'') AS program_name
,es.login_time
FROM sys.dm_exec_sessions es
    INNER JOIN sys.dm_exec_requests ec2 ON es.session_id = ec2.blocking_session_id
    LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
    LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
    LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
    LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id
    LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address
    CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS st
where es.is_user_process = 1 
  and es.session_id <> @@spid
  • You can see the last batch submitted using DBCC INPUTBUFFER(75); - this may or may not be useful, depending on the command(s) and nest level. – Aaron Bertrand Aug 20 '15 at 14:02
  • I was thinking about a DMV so that I could join it in, I am trying to avoid DBCC also. I am having a look at sys.sysprocesses at the moment. – Marcello Miorelli Aug 20 '15 at 14:05
  • Don't use sys.sysprocesses, it's deprecated. And why do you need to join? Are you trying to troubleshoot an issue, or make a pretty report? – Aaron Bertrand Aug 20 '15 at 14:23
  • I actually have a procedure that I use in all my servers, just I noticed that when there is a process blocking another process, if the blocking process is suspended or sleeping it does not show on the results, and I would like to see what is blocking. I will update my question to clarify, I will add my procedure. – Marcello Miorelli Aug 20 '15 at 14:27
  • 1
    Instead of reinventing wheels that others have already solved, have you tried Adam Machanic's sp_WhoIsActive, or considered a 3rd party monitoring tool that doesn't require you to catch these things in the act? – Aaron Bertrand Aug 20 '15 at 14:46
3

How can I find the code of the blocking session, in this case, the session 75 when the session's status is 'sleeping'?

New version this new version shows also the blocking session, however, I could not find out how to get the database name and other data from a sleeping session.

You can use

SELECT db_name(S.database_id) AS DatabaseName,
        ST.text
FROM   sys.dm_exec_connections AS C
       JOIN sys.dm_exec_sessions AS S ON S.session_id = C.session_id
       OUTER APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST
WHERE  C.session_id = 75;
3

You can use Adam Machanic's SP_WHOISACTIVE to get the details tracked.

One of the great things about sp_whoisactive is that it’s very quick to generate a table to collect the data you need, then run the procedure in a loop with a wait statement to regularly collect the data you want over an interval. This usage is documented.

Please see on how to collect the data for same here :Collecting Data from sp_WhoIsActive in a Table

  • 2
    To supplement this answer, make sure you use @show_sleeping_spids = 1 and @find_block_leaders = 1 when using sp_whoIsActive to get complete blocking details. – Kin Shah Aug 25 '15 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.