Executing Process Details

,

Being able to determine the processes that are actively running on an instance of SQL Server at a given time, along with durations, wait types, and the submitted command versus executing command, is extremely valueable for any administrator. This script has helped me countless times identify lead blockers and diferenciate between executing commands and submitted commands. I hope this helps the community as much as it has for my teams.

USE [master];
GO
SET NOCOUNT ON;

DECLARE @Today DATETIME 
    ,   @sqlcmd NVARCHAR (MAX)
    ,   @i INT
    ,   @MaxRID INT
    ,   @SPID INT
    
SELECT @Today = GETDATE();

DECLARE @RunTimeInfo TABLE
(
    RID                 INT IDENTITY (1,1) NOT NULL
,   DBName              NVARCHAR (256) NOT NULL
,   blocking_session_id SMALLINT NULL
,   session_id          SMALLINT NOT NULL
,   client_net_address  VARCHAR  (48) NULL
,   hostname            NVARCHAR (256) NULL
,   loginame            NVARCHAR (256) NULL
,   [Duration]          VARCHAR (25) NULL
,   wait_type           NVARCHAR (120) NOT NULL
,   wait_time	        INT NOT NULL
,   wait_resource       NVARCHAR (512) NOT NULL
,   [status]            NVARCHAR (60) NOT NULL
,   [program_name]      NVARCHAR (256) NULL
,   command	            NVARCHAR (32) NOT NULL
,   objectid            INT NULL
,   QueryText           NVARCHAR (MAX) NULL
,   InputBuffer         NVARCHAR (4000) NULL
,   percent_complete    REAL NULL
,   connect_time        DATETIME NOT NULL
,   start_time          DATETIME NOT NULL
,   cpu_time            INT NULL
);

DECLARE @InputBuffer TABLE
(
    EventType NVARCHAR (4000)
,   [PARAMETERS] INT
,   EventInfo NVARCHAR (4000)
)

INSERT INTO @RunTimeInfo
    (DBName, blocking_session_id, session_id, client_net_address, hostname, loginame
    ,[Duration], wait_type, wait_time, wait_resource, [status], [program_name]
    ,command, objectid, QueryText, percent_complete, connect_time, start_time, cpu_time
    )
SELECT  db.Name AS [DBName]
    ,   req.blocking_session_id
    ,   req.session_id
    ,   con.client_net_address
    ,   pro.hostname
    ,   pro.loginame
    ,   CASE WHEN DATEDIFF( HOUR,req.start_time,@Today ) > 24 THEN
                CAST(DATEDIFF( DAY,req.start_time,@Today ) AS VARCHAR(18)) + ' Day(s)'
            ELSE
                RIGHT(REPLICATE('0',2)+CAST( (((DATEDIFF( millisecond,req.start_time,@Today )/1000)/60)/60)%60 AS VARCHAR ),2) +':' --Hours
               +RIGHT(REPLICATE('0',2)+CAST( ((DATEDIFF( millisecond,req.start_time,@Today )/1000)/60)%60 AS VARCHAR ),2) +':'      --Minutes
               +RIGHT(REPLICATE('0',2)+CAST( (DATEDIFF( millisecond,req.start_time,@Today )/1000)%60 AS VARCHAR ),2) +'.'           --Seconds
               +RIGHT(REPLICATE('0',3)+CAST( DATEDIFF( millisecond,req.start_time,@Today )%1000 AS VARCHAR ),3)                     --Milliseconds
        END AS [Duration (hh:mm:ss.nnn)]
    ,   ISNULL(req.wait_type,'') AS wait_type
    ,   req.wait_time
    ,   req.wait_resource
    ,   req.[status]
    ,   pro.[program_name]
    ,   req.command
    ,   txt.objectid
    ,   ISNULL(CAST(OBJECT_NAME(txt.objectid,DB_ID(db.name)) AS NVARCHAR(MAX)),txt.[text]) AS [QueryText]
    ,   req.percent_complete
    ,   con.connect_time
    ,   req.start_time
    ,   cpu_time

FROM    sys.dm_exec_connections con INNER JOIN
        ( SELECT spid
            ,    LTRIM( RTRIM( hostname ) ) AS [hostname]
            ,    LTRIM( RTRIM( loginame ) ) AS [loginame]
            ,    LTRIM( RTRIM( [program_name] ) ) AS [program_name]
          FROM  sys.sysprocesses 
          WHERE ASCII(LTRIM( RTRIM( loginame ) )) IS NOT NULL
        ) pro ON con.session_id = pro.spid INNER JOIN
        sys.dm_exec_requests req ON con.connection_id = req.connection_id INNER JOIN
        sys.databases db ON req.database_id = db.database_id CROSS APPLY
        sys.dm_exec_sql_text( req.[sql_handle] ) AS txt 
        
WHERE   ISNULL(req.wait_type,'') NOT IN 
                             ( 'BAD_PAGE_PROCESS','BROKER_EVENTHANDLER','BROKER_RECEIVE_WAITFOR','BROKER_TASK_STOP'
                              ,'BROKER_TO_FLUSH','BROKER_TRANSMITTER','CHECKPOINT_QUEUE','CHKPT','CLR_SEMAPHORE'
                              ,'DBMIRROR_EVENTS_QUEUE','FT_IFTS_SCHEDULER_IDLE_WAIT','KSOURCE_WAKEUP','LAZYWRITER_SLEEP'
                              ,'LOGMGR_QUEUE','MISCELLANEOUS','ONDEMAND_TASK_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH'
                              ,'RESOURCE_QUEUE','SLEEP_SYSTEMTASK','SLEEP_TASK','SQLTRACE_BUFFER_FLUSH'
                              ,'XE_DISPATCHER_WAIT','XE_TIMER_EVENT','WAITFOR'
                             )
        AND req.session_id != @@SPID 
        
ORDER BY 
        db.Name
    ,   req.session_id;
    
SELECT  @sqlcmd = N'DBCC INPUTBUFFER(@session_id)'
    ,   @i = 0
    ,   @MaxRID = MAX(RID)
FROM    @RunTimeInfo;

WHILE   @i <= @MaxRID
 BEGIN
 
    IF (    SELECT  objectid 
            FROM    @RunTimeInfo 
            WHERE   RID = @i
        ) IS NULL
     BEGIN
        SELECT  @i = @i + 1;
        DELETE  @InputBuffer;
        CONTINUE;
     END
     
    SELECT  @SPID = session_id
    FROM    @RunTimeInfo
    WHERE   RID = @i;
    
    INSERT INTO @InputBuffer
    EXECUTE sp_executesql 
                @sqlcmd
            ,   N'@session_id int'
            ,   @SPID;
    
    UPDATE  @RunTimeInfo
    SET     InputBuffer = (SELECT EventInfo FROM @InputBuffer)
    WHERE   RID = @i;
    
    SELECT @i = @i + 1
    DELETE  @InputBuffer
 END

SELECT  DBName
    ,   blocking_session_id
    ,   session_id
    ,   client_net_address
    ,   hostname
    ,   loginame
    ,   [Duration]
    ,   wait_type
    ,   wait_time
    ,   wait_resource
    ,   [status]
    ,   [program_name]
    ,   command
    ,   objectid
    ,   QueryText
    ,   InputBuffer
    ,   percent_complete
    ,   connect_time
    ,   start_time
    ,   cpu_time
FROM    @RunTimeInfo

Rate

3 (3)

Share

Share

Rate

3 (3)