Modified SP_WHOM

  • Comments posted to this topic are about the item Modified SP_WHOM

  • Could the author clarify the business or technical purpose of this script?

  • This script is used for tracking blocking issues or just monitoring sessions on any database server.

    Cheers

  • I've got a Stored Procedure some what like this but I've also added:

    DBCC InputBuffer(<SPId Variable>);

    for each SPId in the result set. It helps to know what command was/is executing.

    One of the problems with this type of routine is that work comes and goes on a busy system such that in between the separate queries you have to do to collect all the relevant information some of the SPIds may drop or reconnect messing up the consistency of the collected information. But this doesn't seem to happen very often.

    Ha! Love the grammatically correct procedure name!



    PeteK
    I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

  • That is a great point! I have the following similar version for those interested.

    USE [master]

    GO

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_whom1]') AND type in (N'P', N'PC'))

    DROP PROCEDURE [dbo].[sp_whom1]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROCEDURE [dbo].[sp_whom1] AS

    --------------------------------------------------------------------------------------------------

    --

    -- Script Name: GetSessionSQLText.sql

    --

    -- Author: David Kranes

    --

    -- Description: This script will return each spid and it's corresponding SQL text. Supply

    -- desired SPID or ALL non-system SPID's will be returned.

    --

    -- Parameters:

    -- 1) @desSPID - Limits results to just this SPID. Set below in the script.

    --

    -- Revision History:

    -- 1) 1/26/2012 - [DBK] - Created.

    --

    --------------------------------------------------------------------------------------------------

    SET NOCOUNT ON

    IF OBJECT_ID('tempdb..#curSQL') IS NOT NULL

    DROP TABLE #curSQL

    CREATE TABLE #curSQL

    (spID SMALLINT,

    blocked VARCHAR(25),

    hostName VARCHAR(2000),

    dbName VARCHAR(100),

    cmd VARCHAR(100),

    sqlText NTEXT,

    sqlcmd NTEXT,

    phyIO BIGINT,

    Status VARCHAR(100),

    programName VARCHAR(1000),

    loginTime DATETIME,

    lastBatch DATETIME

    )

    DECLARE @spID smallint,

    @Blocked VARCHAR(25),

    @sqltext VARCHAR(max),

    @sqlcmd VARCHAR(max),

    @physio BIGINT,

    @hostName VARCHAR(2000),

    @desSPID SMALLINT,

    @dbName VARCHAR(100),

    @Cmd VARCHAR(100),

    @status VARCHAR(100),

    @programName VARCHAR(1000),

    @loginTime DATETIME,

    @lastBatch DATETIME

    -- Set desired SPID. If NULL, it will return all non-system SPID's.

    -- SET @desSPID = 4704

    BEGIN TRY

    IF @desSPID IS NOT NULL

    DECLARE spID_cursor CURSOR

    FORWARD_ONLY READ_ONLY

    FOR SELECT spid, CASE blocked WHEN 0 THEN 'NO' ELSE 'YES BY SPID: ' + CAST(blocked AS VARCHAR) END blocked, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch

    FROM sys.sysprocesses

    WHERE spid = @desSPID

    GROUP BY spid, blocked, physical_io, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch

    ORDER BY spid

    ELSE

    DECLARE spID_cursor CURSOR

    FORWARD_ONLY READ_ONLY

    FOR SELECT spid, CASE blocked WHEN 0 THEN 'NO' ELSE 'YES BY SPID: ' + CAST(blocked AS VARCHAR) END blocked, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch

    FROM sys.sysprocesses

    WHERE spid > 50 and spid <> @@SPID and status IN ('running', 'runnable', 'suspended')

    GROUP BY spid, blocked, physical_io, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch

    ORDER BY spid

    OPEN spID_cursor

    FETCH NEXT

    FROM spID_cursor

    INTO @spID, @Blocked, @hostName, @dbName, @cmd, @physio, @status, @programName, @loginTime, @lastBatch

    WHILE @@FETCH_STATUS = 0

    BEGIN

    /*

    SELECT @sqltext = sql_handle

    FROM sys.sysprocesses

    WHERE spid = @spID

    */

    SELECT

    @sqltext = A.text,

    @sqlcmd = SUBSTRING(A.text, sp.stmt_start / 2,

    (CASE WHEN sp.stmt_end = -1 THEN DATALENGTH(A.text)ELSE sp.stmt_end END - sp.stmt_start )/2)

    FROM

    sys.sysprocesses sp

    OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) as A

    WHERE

    spid = @spID

    INSERT INTO #curSQL

    (spID, blocked, hostName, dbName, cmd, sqlText, sqlcmd, phyIO, Status, programName, loginTime, lastBatch)

    VALUES(@spID, @Blocked, @hostName, @dbName, @cmd, @sqltext, @sqlcmd, @physio, @status, @programName, @loginTime, @lastBatch)

    -- SELECT @spID, @Blocked, @hostName, @dbName, @cmd, @sqltext, @sqlcmd, @physio, @status, @programName, @loginTime, @lastBatch

    --FROM ::fn_get_sql(@sqltext)

    FETCH NEXT

    FROM spID_cursor

    INTO @spID, @Blocked, @hostName, @dbName, @cmd, @physio, @status, @programName, @loginTime, @lastBatch

    END

    CLOSE spID_cursor

    DEALLOCATE spID_cursor

    SELECT * FROM #curSQL ORDER BY blocked desc, spid

    -- If there is blocking, print out blocking info.

    IF (SELECT sum(blocked) from sys.sysprocesses WHERE spid > 50) > 0

    --IF (SELECT TOP (1) COUNT(*) FROM #curSQL WHERE blocked = 'YES') > 0

    BEGIN

    /*SELECT x.session_id AS 'Blocked Session ID',

    (

    -- Query gets XML text for the sql query for the session_id

    SELECT text AS [text()]

    FROM sys.dm_exec_sql_text(x.sql_handle)

    FOR XML PATH(''), TYPE

    )AS 'Blocked SQL Text',

    x.host_name,

    x.login_name,

    x.start_time,

    x.totalReads,

    x.totalWrites,

    x.totalCPU,

    x.writes_in_tempdb,

    COALESCE(x.blocking_session_id, 0) AS 'Blocking Session ID',

    (

    SELECT p.text

    FROM

    (

    -- Query gets the corresponding sql_handle info to find the XML text in the next query

    SELECT MIN(sql_handle) AS sql_handle

    FROM sys.dm_exec_requests r2

    WHERE r2.session_id = x.blocking_session_id

    ) AS r_blocking

    CROSS APPLY

    (

    -- Query will pull back the XML text for a blocking session if there is any from the sql_haldle

    SELECT text AS [text()]

    FROM sys.dm_exec_sql_text(r_blocking.sql_handle)

    FOR XML PATH(''), TYPE

    ) p (text)

    ) AS 'Blocking Text'

    FROM

    (

    -- Query returns active session_id and metadata about the session for resource, blocking, and sql_handle

    SELECT r.session_id,

    s.host_name,

    s.login_name,

    r.start_time,

    r.sql_handle,

    r.blocking_session_id,

    SUM(r.reads) AS totalReads,

    SUM(r.writes) AS totalWrites,

    SUM(r.cpu_time) AS totalCPU,

    SUM(tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb

    FROM sys.dm_exec_requests r

    JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id

    JOIN sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id

    --JOIN #curSQL cs ON r.session_id = cs.spID

    WHERE r.status IN ('running', 'runnable', 'suspended') AND

    r.blocking_session_id <> 0 AND

    r.session_id <> @@SPID

    GROUP BY r.session_id,

    s.host_name,

    s.login_name,

    r.start_time,

    r.sql_handle,

    r.blocking_session_id

    ) x */

    select

    'LEAD BLOCKER: ' + CAST(spid as varchar(20)), loginame, hostname, cpu, memusage, physical_io, *

    from

    master..sysprocesses a

    where

    exists ( select b.*

    from master..sysprocesses b

    where b.blocked > 0 and

    b.blocked = a.spid ) and not

    exists ( select b.*

    from master..sysprocesses b

    where b.blocked > 0 and

    b.spid = a.spid )

    order by spid

    END-- If there is blocking, print out blocking info.

    END TRY

    BEGIN CATCH

    CLOSE spID_cursor

    DEALLOCATE spID_cursor

    SELECT ERROR_NUMBER() ErrorNBR, ERROR_SEVERITY() Severity, ERROR_LINE() ErrorLine, ERROR_MESSAGE() Msg

    PRINT 'There was an error in the script.'

    END CATCH

    Cheers

  • FYI,

    DBCC InputBuffer(54);

    is problematic. It may be better to use the following as it can be used in a row set query:

    Select

    s.session_id[SPId]

    ,SubString

    (

    st.text,

    (r.statement_start_offset/2)+1,

    ((Case r.statement_end_offsetwhen -1 then DataLength(st.text)else r.statement_end_offset End-r.statement_start_offset)/2)+1

    )[Command String]

    from sys.dm_exec_sessions as s

    join sys.dm_exec_requests as r on r.session_id = s.session_id

    cross apply sys.dm_exec_sql_text(r.sql_handle) as st

    order by s.last_request_end_time;

    🙂



    PeteK
    I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

  • Thanks. I combined this script with what Peter wrote to get information about what's currently active.

  • What advantages does this have over sp_whoIsActive?

  • oradbguru (1/23/2014)


    This script is used for tracking blocking issues or just monitoring sessions on any database server.

    Great, I'll give it a try.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply