Return Query Text Along With sp_who2 Using SQL 2000

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

  • 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

  • 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

  • Great and very useful article. 🙂

  • 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

    )

  • Extremely useful - much appreciated!

  • Excellent. This will be very useful.

  • 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

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

    ATBCharles Kincaid

  • 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.

  • 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

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

    Thanks for your article.

    David Bird

  • 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

  • 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.

  • 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 14 (of 14 total)

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