Return Query Text Along With sp_who2 Using SQL 2000

  • KenSimmons

    SSCertifiable

    Points: 7822

    Comments posted to this topic are about the item Return Query Text Along With sp_who2 Using SQL 2000

  • KenSimmons

    SSCertifiable

    Points: 7822

    The code from the article does not copy/paste correctly, so I am posting it here. I have noticed that if you paste it into Word or Open Office it will display correctly.

    CREATE TABLE #sp_who2

    (

    SPID INT,

    Status VARCHAR(1000) NULL,

    Login SYSNAME NULL,

    HostName SYSNAME NULL,

    BlkBy SYSNAME NULL,

    DBName SYSNAME NULL,

    Command VARCHAR(1000) NULL,

    CPUTime INT NULL,

    DiskIO INT NULL,

    LastBatch VARCHAR(1000) NULL,

    ProgramName VARCHAR(1000) NULL,

    SPID2 INT

    )

    Create Table #SqlStatement

    (spid int,

    statement varchar(8000))

    create table #temp (x varchar(100), y int, s varchar(1000), id int

    identity (1,1))

    INSERT #sp_who2 EXEC sp_who2

    Declare @spid varchar(10)

    Declare @Statement varchar(8000)

    declare @sql varchar(1000)

    DECLARE SpidCursor Cursor

    FOR Select spid from #sp_who2

    OPEN SpidCursor

    FETCH NEXT FROM SpidCursor

    INTO @spid

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @sql = 'dbcc inputbuffer (' + @spid + ')'

    insert #temp

    exec (@sql)

    Insert Into #SqlStatement

    Select @spid, s From #Temp where id = (Select max(id) from #Temp)

    FETCH NEXT FROM SpidCursor

    INTO @spid

    END

    Close SpidCursor

    Deallocate SpidCursor

    Select B.Statement, A.* from #sp_who2 A Left JOIN

    #SqlStatement B ON A.spid = B.spid

    Drop Table #Temp

    Drop Table #SqlStatement

    Drop Table #sp_who2

  • philcart

    SSC-Forever

    Points: 47713

    How about using fn_get_sql instead of dbcc inputbuffer? That way the sql statement won't be truncated. 😉

    --------------------
    Colt 45 - the original point and click interface

  • Anipaul

    SSC-Insane

    Points: 24681

    Great and very useful article. 🙂

  • jogos

    SSC Enthusiast

    Points: 115

    It works fine for SQL 2005 if you add an extra column to the create statement of #sp_who2

    CREATE TABLE #sp_who2 ( SPID INT, Status VARCHAR(1000) NULL, Login SYSNAME NULL

    , HostName SYSNAME NULL, BlkBy SYSNAME NULL, DBName SYSNAME NULL

    , Command VARCHAR(1000) NULL, CPUTime INT NULL, DiskIO INT NULL

    , LastBatch VARCHAR(1000) NULL, ProgramName VARCHAR(1000) NULL, SPID2 INT

    , RequestId INT -- Extra column for SQL2005

    )

  • rodonoghue

    Valued Member

    Points: 59

    Extremely useful - much appreciated!

  • Ken Davis

    SSCarpal Tunnel

    Points: 4329

    Excellent. This will be very useful.

  • LP-181697

    SSC Eights!

    Points: 966

    The only one problem, in multi-user environment and with quick transactions the time between taking is with sp_who and looping through those ids is enough that process is ended and another process assigned with this id. Then you get wrong inputbuffer output. We have it all the time with 2,000 users and 50-60 transactions per second. Sometime 10-15% of id returns wrong inputbuffer or disappeared while loop is running

  • Charles Kincaid

    SSChampion

    Points: 13593

    So would it be possible to rewrite this so as to avoid the cursor?

    ATBCharles Kincaid

  • dbishop

    Mr or Mrs. 500

    Points: 565

    You might do a search in Google for sp_who_3 (http://vyaskn.tripod.com/sp_who3.htm). Very robust with lots of optional parameters and already built to show the input buffer and a lot more than sp_who_2.

  • KenSimmons

    SSCertifiable

    Points: 7822

    philcart (7/18/2008)


    How about using fn_get_sql instead of dbcc inputbuffer? That way the sql statement won't be truncated. 😉

    Here is the fn_get_sql version, but it will strip out any statement that may have a password. Also, you have to change the "Maximum characters per column" in query analyzer to more than 256 to see the results.

    CREATE TABLE #sp_who2

    (

    SPID INT,

    Status VARCHAR(1000) NULL,

    Login SYSNAME NULL,

    HostName SYSNAME NULL,

    BlkBy SYSNAME NULL,

    DBName SYSNAME NULL,

    Command VARCHAR(1000) NULL,

    CPUTime INT NULL,

    DiskIO INT NULL,

    LastBatch VARCHAR(1000) NULL,

    ProgramName VARCHAR(1000) NULL,

    SPID2 INT

    )

    Create Table #SqlStatement

    (spid int,

    statement varchar(8000))

    create table #temp (dbid varchar(100), objectid varchar(100), number varchar(100), encrypted varchar(100), stmt varchar(8000), id int identity (1,1))

    INSERT #sp_who2 EXEC sp_who2

    Declare @spid varchar(10)

    Declare @Statement varchar(8000)

    declare @sql varchar(1000)

    DECLARE @Handle binary(20)

    DECLARE SpidCursor Cursor

    FOR Select spid from #sp_who2

    OPEN SpidCursor

    FETCH NEXT FROM SpidCursor

    INTO @spid

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = @spid

    insert #temp

    SELECT * FROM ::fn_get_sql(@Handle)

    Insert Into #SqlStatement

    Select @spid, stmt From #Temp where id = (Select max(id) from #Temp)

    FETCH NEXT FROM SpidCursor

    INTO @spid

    END

    Close SpidCursor

    Deallocate SpidCursor

    Select B.Statement, A.* from #sp_who2 A Left JOIN

    #SqlStatement B ON A.spid = B.spid

    Drop Table #Temp

    Drop Table #SqlStatement

    Drop Table #sp_who2

  • David Bird

    SSCarpal Tunnel

    Points: 4669

    Your approach for combining the output from a stored procedure and a DBCC Command is very helpful.

    Thanks for your article.

    David Bird

  • Lewis Dowson

    SSC Eights!

    Points: 878

    Better solution, without cursors or temp tables

    select distinct

    s.session_id as spid,

    cast( convert(varchar, dateadd(second, datediff(ss, s.last_request_start_time, getdate()),'' ), 108) as varchar(8))[delta],

    r.cpu_time / nullif(datediff(ss, s.last_request_start_time, getdate()),0) [cpu/sec],

    (r.reads + r.writes) / nullif(datediff(ss, s.last_request_start_time, getdate()),0) [diskio/sec],

    isnull(w.blocking_session_id, 0) as blockedby,

    s.host_name,

    s.login_name as login,

    db_name(r.database_id) as dbname,

    s.program_name,

    s.client_interface_name,

    s.status,

    r.cpu_time as cpu,

    r.granted_query_memory as memory,

    r.reads,

    r.writes,

    r.logical_reads,

    r.row_count,

    cast(r.percent_complete as dec(4,1)) as pct_complete,

    r.command,

    t.text,

    c.client_net_address

    from sys.dm_exec_sessions s

    left join sys.dm_os_waiting_tasks w on s.session_id = w.session_id

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

    left join sys.dm_exec_connections c on s.session_id = c.session_id

    outer apply sys.dm_exec_sql_text(plan_handle) t

    where s.status != 'sleeping' -- // no inactive processes

    and s.session_id != @@spid

  • KenSimmons

    SSCertifiable

    Points: 7822

    Lewis,

    The Dynamic Management Views are not available in SQL 2000, but I agree if you are using 2005 DMV's are the way to go.

  • Martin-450785

    SSC Journeyman

    Points: 88

    Ken,

    Thank you Ken for supplying us with your briliant script compilation for the SQL Activity Process Monitoring.

    Works like a charm... 😉

    Cheers

    Martin

    SELECT * FROM users WHERE clue > 0 = 0 rows returned

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

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