Return Query Text Along With sp_who2 Using Dynamic Management Views

  • KenSimmons

    SSCertifiable

    Points: 7822

    Comments posted to this topic are about the item Return Query Text Along With sp_who2 Using Dynamic Management Views

  • sheetalsh

    SSC-Addicted

    Points: 439

    Hi,

    Could you please elaborate reg what you did in that query as I am not getting it?

    You can please provide the acript for the tables you are using so that at least we can execute the query to see the result.

    I am working in SQL server 2005.

    Thanks & Regards,

    SS

  • DanKennedy

    Hall of Fame

    Points: 3736

    Here's a similar one I use which uses a neat trick (credit to Adam Machanic) that returns the statement as xml so it can be clicked and opened in a new window. Unfortunately it's xml encoded but very convenient.

    USE [master]

    GO

    /****** Object: StoredProcedure [dbo].[sp_who3] Script Date: 06/26/2008 09:40:54 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROCEDURE [dbo].[sp_who3]

    (

    @filter tinyint = 1,

    @filterspid int = NULL

    )

    AS

    SET NOCOUNT ON;

    DECLARE @processes TABLE

    (

    spid int,

    blocked int,

    databasename varchar(256),

    hostname varchar(256),

    program_name varchar(256),

    loginame varchar(256),

    status varchar(60),

    cmd varchar(128),

    cpu int,

    physical_io int,

    [memusage] int,

    login_time datetime,

    last_batch datetime,

    current_statement_parent xml,

    current_statement_sub xml)

    INSERT INTO @processes

    SELECT sub.*

    FROM

    (

    SELECT sp.spid,

    sp.blocked,

    sd.name,

    RTRIM(sp.hostname) AS hostname,

    RTRIM(sp.[program_name]) AS [program_name],

    RTRIM(sp.loginame) AS loginame,

    RTRIM(sp.status) AS status,

    sp.cmd,

    sp.cpu,

    sp.physical_io,

    sp.memusage,

    sp.login_time,

    sp.last_batch,

    (

    SELECT

    LTRIM(st.text) AS [text()]

    FOR XML PATH(''), TYPE

    ) AS parent_text,

    (

    SELECT

    LTRIM(CASE

    WHEN LEN(COALESCE(st.text, '')) = 0 THEN NULL

    ELSE SUBSTRING(st.text, (er.statement_start_offset/2)+1,

    ((CASE er.statement_end_offset

    WHEN -1 THEN DATALENGTH(st.text)

    ELSE er.statement_end_offset

    END - er.statement_start_offset)/2) + 1)

    END) AS [text()]

    FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st

    WHERE er.session_id = sp.spid

    FOR XML PATH(''), TYPE

    ) AS child_text

    FROM sys.sysprocesses sp WITH (NOLOCK) LEFT JOIN sys.sysdatabases sd WITH (NOLOCK) ON sp.dbid = sd.dbid

    CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS st

    ) sub INNER JOIN sys.sysprocesses sp2 ON sub.spid = sp2.spid

    ORDER BY

    sub.spid

    -- if specific spid required

    IF @filterspid IS NOT NULL

    DELETE @processes

    WHERE spid <> @filterspid

    -- remove system processes

    IF @filter = 1 OR @filter = 2

    DELETE @processes

    WHERE spid < 51

    OR spid = @@SPID

    -- remove inactive processes

    IF @filter = 2

    DELETE @processes

    WHERE status = 'sleeping'

    AND cmd IN ('AWAITING COMMAND')

    AND blocked = 0

    SELECT spid,

    blocked,

    databasename,

    hostname,

    loginame,

    status,

    current_statement_parent,

    current_statement_sub,

    cmd,

    cpu,

    physical_io,

    program_name,

    login_time,

    last_batch

    FROM @processes

    ORDER BY spid

    RETURN 0;

  • KenSimmons

    SSCertifiable

    Points: 7822

    Here is the script. I am not sure why it will not copy correctly from the article. When you copy the script from the article there is no space between "A.program_nameFROM".

    SELECT

    D.text SQLStatement,

    A.Session_ID SPID,

    ISNULL(B.status,A.status) Status,

    A.login_name Login,

    A.host_name HostName,

    C.BlkBy,

    DB_NAME(B.Database_ID) DBName,

    B.command,

    ISNULL(B.cpu_time, A.cpu_time) CPUTime,

    ISNULL((B.reads + B.writes),(A.reads + A.writes)) DiskIO,

    A.last_request_start_time LastBatch,

    A.program_name

    FROM

    sys.dm_exec_sessions A

    LEFT JOIN

    sys.dm_exec_requests B

    ON A.session_id = B.session_id

    LEFT JOIN

    (

    SELECT

    A.request_session_id SPID,

    B.blocking_session_id BlkBy

    FROM sys.dm_tran_locks as A

    INNER JOIN sys.dm_os_waiting_tasks as B

    ON A.lock_owner_address = B.resource_address) C

    ON A.Session_ID = C.SPID

    OUTER APPLY sys.dm_exec_sql_text(sql_handle) D

  • leea

    SSC Enthusiast

    Points: 186

    I so wish this worked with 2000

  • Jeff Moden

    SSC Guru

    Points: 994937

    I've not tried this because the production environment I work in is stuck in 2k... but nice short too-the-point article. Thanks. And, I agree... I wish it worked in 2k.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • sheetalsh

    SSC-Addicted

    Points: 439

    Hi Ken,

    It's giving error as follows:

    Msg 321, Level 15, State 1, Line 28

    "sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

    Thanks,

    SS

  • Marc Bujold

    SSC Enthusiast

    Points: 107

    sheetalsh (6/26/2008)


    Hi Ken,

    It's giving error as follows:

    Msg 321, Level 15, State 1, Line 28

    "sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

    Thanks,

    SS

    I get this error as well.

    Marc

  • richard_jm

    Valued Member

    Points: 63

    How close to this script can you get in SQL Server 2000? I'm stuck there for awhile

    Thanks!

  • Jerry Hung

    SSChampion

    Points: 12908

    I copied the code from the article verbatim

    ran fine in my SQL 2005 boxes (build 3042 and 2047)

    I am guessing it won't run in SQL 2000

    Dan: your code has a WINK in it, ha ha... when it should be the closing bracket for the TABLE declaration

    SQLServerNewbie MCITP: Database Administrator SQL Server 2005
  • KenSimmons

    SSCertifiable

    Points: 7822

    sheetalsh (6/26/2008)


    Hi Ken,

    It's giving error as follows:

    Msg 321, Level 15, State 1, Line 28

    "sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

    Thanks,

    SS

    Right Click on your database and select properties. Under the options tab the compatibility level has to be set to "SQL Server 2005 (90)" for it to work.

  • DAVNovak

    Hall of Fame

    Points: 3070

    Great script. It is nice to have everything together when viewing the results and checking for blocking.

    Dave Novak

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    did something similar a few months ago with the connections and sessions DMV's. it was very nice except took a lot of storage. if we did this on a regular basis it would probably run 200GB - 300GB a month for 2-3 servers. i dumped the data into a table on the local server and every 15 minutes had an SSIS job copy it to a reporting server and delete all data in the local table.

  • john.vanda

    Old Hand

    Points: 334

    I'm digging this solution. I really like to be able to filter my results, so I took the code from Dan (nice code Dan) that used the trick from Adam Machanic, and turned it into a table valued function:

    USE [master]

    GO

    /****** Object: UserDefinedFunction [dbo].[fn_sp_who4] Script Date: 06/26/2008 11:31:07 ******/

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER FUNCTION [dbo].[fn_sp_who4]

    (@filterspid int = NULL, @filter tinyint = 1)

    RETURNS @processes TABLE

    (

    spid int,

    blocked int,

    databasename varchar(256),

    hostname varchar(256),

    current_statement_parent xml,

    current_statement_sub xml,

    program_name varchar(256),

    loginame varchar(256),

    status varchar(60),

    cmd varchar(128),

    cpu int,

    physical_io int,

    [memusage] int,

    login_time datetime,

    last_batch datetime

    )

    AS

    BEGIN

    INSERT INTO @processes

    SELECT sub.*

    FROM

    ( SELECT sp.spid,

    sp.blocked,

    sd.name,

    RTRIM(sp.hostname) AS hostname,

    ( SELECT LTRIM(st.text) AS [text()]

    FOR XML PATH(''), TYPE) AS parent_text,

    ( SELECT

    LTRIM(CASE

    WHEN LEN(COALESCE(st.text, '')) = 0 THEN NULL

    ELSE SUBSTRING(st.text, (er.statement_start_offset/2)+1,

    ((CASE er.statement_end_offset

    WHEN -1 THEN DATALENGTH(st.text)

    ELSE er.statement_end_offset

    END - er.statement_start_offset)/2) + 1)

    END) AS [text()]

    FROM sys.dm_exec_requests er

    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st

    WHERE er.session_id = sp.spid

    FOR XML PATH(''), TYPE) AS child_text,

    RTRIM(sp.[program_name]) AS [program_name],

    RTRIM(sp.loginame) AS loginame,

    RTRIM(sp.status) AS status,

    sp.cmd,

    sp.cpu,

    sp.physical_io,

    sp.memusage,

    sp.login_time,

    sp.last_batch

    FROM sys.sysprocesses sp (NOLOCK)

    LEFT JOIN sys.sysdatabases sd WITH (NOLOCK) ON sp.dbid = sd.dbid

    CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS st

    ) sub

    INNER JOIN sys.sysprocesses sp2 ON sub.spid = sp2.spid

    ORDER BY sub.spid

    -- if specific spid required

    IF @filterspid IS NOT NULL

    DELETE @processes

    WHERE spid <> @filterspid

    -- remove system processes

    IF @filter = 1 OR @filter = 2

    DELETE @processes

    WHERE spid < 51 OR spid = @@SPID

    -- remove inactive processes

    IF @filter = 2

    DELETE @processes

    WHERE status = 'sleeping'

    AND cmd IN ('AWAITING COMMAND')

    AND blocked = 0

    RETURN

    END

    Then you can run

    SELECT ... from master.dbo.fn_sp_who4(@filterspid = , @filter = )

    WHERE ...

    or

    SELECT * from master.dbo.fn_sp_who4(default,default) for the function parameter defaults

    You could also technically remove the parameters entirely since it's a table valued function after all and just use the where clause in the SELECT.

    It's rough, but I like it 😀

    --John Vanda

    SQL Junior DBA

  • SanjayAttray

    SSChampion

    Points: 13157

    Thanks John and Ken for these scripts. Both are good and are worth keeping it handy.

    SQL DBA.

Viewing 15 posts - 1 through 15 (of 23 total)

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