SQL Server Performance

Trying to get the Text/SQL That is Causing Blocks

Discussion in 'SQL Server 2005 General DBA Questions' started by jbates99, Jun 25, 2009.

  1. jbates99 Member

    hi everyone,

    This is 2005 Std SP2.

    I would like to log blocing info into a table over a 24 hour period.

    DBCC inputbuffer(YourSPID) is good for ad-hoc, on-demand inquiry.

    But DBCC inputbuffer(@VariableSPID) gives a syntax error so I am exploring other methods.

    Someone suggested using the following DMVs but when I execute the block of code (below) it never prints anything, even when I hard-code a good spid#.

    As I said, I want to automate and schedule the collection of blocking info so I can investigate those stored procedures.
    Thanks for any ideas, John

    DECLARE
    @BlockingSPID varchar(6);declare
    @text varchar(256);SET
    @BlockingSPID = 56 set
    @text = (select text from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(sql_handle) where
    session_id = @BlockingSPID);print
    @text;
  2. alexsqlforum New Member

    Use this stored procedure
    http://support.microsoft.com/kb/271509
  3. Sofya New Member

    I was trying to capture information about top CPU users and met the same problem --
    exec (DBCC inputbuffer (@ID) gives me syntax error.

    So, i had to use workaround -- created intermediate table and inserted there generated commands with real spid numbers fro sp_who2 procedure. Then using cursor executed these commands and stored information in the summary table.

    Below is the script that i have created.
    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id
    = object_id(N'[tempdb]..[#who]')) drop table #who
    create table #who (
    spid int,
    Status varchar (75),
    Login varchar (75),
    HostName varchar (175),
    BlkBy varchar (55),
    DBname varchar (175),
    command varchar (155),
    CPUTime int,
    DISKIO int,
    Last_Batch varchar (55),
    ProgramName varchar (155),
    SPID2 int,
    requestid int
    )
    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id =
    object_id(N'[tempdb]..[#who2]')) drop table #who2

    --create table #who2 with generated dbcc statement

    create table #who2 (
    spid int,
    cmd_txt varchar (550)
    )

    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id =
    object_id(N'[tempdb]..[#who4]')) drop table #who4

    --create table #who4 with spid and query text from running DBCC inputbuffer() cmd
    create table #who4 (
    spid int,
    EventType varchar(50)
    ,Parametersvarchar(50)
    , EventInfovarchar(5000)
    )

    insert into #who exec sp_who2 --ACTIVE

    insert into #who2 select spid,'DBCC inputbuffer (' + CAST(spid AS varchar(10))+ ')' from #who
    --select * from #who2
    declare @id int
    declare cur1 cursor for select spid from #who
    declare @cmd varchar(5000)
    open cur1
    fetch next from cur1 into @id
    print @id
    --select @id
    WHILE @@FETCH_STATUS = 0
    BEGIN
    print @cmd
    insert into #who2 values(@id,@cmd);
    fetch next from cur1 into @id
    end

    close cur1
    deallocate cur1

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

    declare cur2 cursor for select spid, cmd_txt from #who2
    open cur2
    fetch next from cur2 into @id, @cmd

    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id =
    object_id(N'[tempdb]..[#who3]')) drop table #who3

    --create table #who3 with query text from running DBCC inputbuffer() cmd
    create table #who3 (
    EventType varchar(50)
    ,Parametersvarchar(50)
    ,EventInfovarchar(5000)
    )

    insert into #who3 exec (@cmd) -- one record table
    insert into #who4 select @id, EventType,parameters,EventInfo from #who3

    fetch next from cur2 into @id,@cmd
    end
    close cur2
    deallocate cur2

    select 'who4',* from #who4 where EventType !='No Event'
    --you may select all or any number other than 10 like top 5 etc
    select top 10 @@servername,a.spid,STATUS,LOGIN,hostname,blkby,Last_Batch,ProgramName,
    dbname,cputime,diskio,Last_Batch,
    b.eventinfo from #who a, #who4 b
    where a.spid =b.spid
    --and DBname like 'your DB%'
    order by cputime desc
    --order by DISKIO
    --

Share This Page