Post reply

finding the EXACT query thats blocking

  • May 21, 2004 at 7:46 am

    #107881

    Good morning,

    I have an home grown application that keeps slowing and down and having lots of blocking. I have looked over this website and found lots of queries and SP's that show me the spid and name of user and database. Is there something out there that shows exactly the query thats causing the blocking?

    This is what I am using so far:

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

    ---this finds the blocking chain.

    IF EXISTS

    (SELECT * FROM master.dbo.sysprocesses

    WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses))

    SELECT spid, status, loginame=SUBSTRING(loginame,1,12),

    hostname=substring(hostname, 1, 12),

    blk=CONVERT(char(3), blocked),

    dbname=SUBSTRING(DB_NAME(dbid), 1, 10), cmd, waittype

    FROM master.dbo.sysprocesses

    WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

    AND blocked=0

    ELSE

    SELECT 'No blocking processes found!'

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

    -- The following script can be run to identify current, long-running transactions. This query provides results based on --the instant is runs, and will vary each time you run it.

    SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]

    FROM master..sysprocesses p

    WHERE open_tran > 0

    AND spid > 50

    AND datediff (s, last_batch, getdate ()) > 30

    ANd EXISTS (SELECT * FROM master..syslockinfo l

    WHERE req_spid = p.spid AND rsc_type <> 2)

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

    use yardcheck

    go

    exec dbo.aba_lockinfo

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

    use master

    go

    select req_spid, count(*)

    from master.dbo.syslockinfo

    group by req_spid

    having count(*)> 10

    Thanks

    Laura

  • May 24, 2004 at 8:00 am

    This was removed by the editor as SPAM

  • May 24, 2004 at 6:19 pm

    #507561

    Sorry I'm not an expert but I've heard of something that might help you. Its the Blocker script from Microsoft:

    http://support.microsoft.com/?id=271509

    "The following description of the sp_blocker_pss80 stored procedure captures this information:

    • The starting time (according to the computer that is running SQL Server) so that this sampling of blocking can be time-aligned with other performance information, such as a Microsoft Windows NT Performance Monitor log or a SQL Profiler log.
    • Information about connections to SQL Server, by querying the sysprocesses system table.
    • Information about lock resources, by querying the syslockinfo system table.
    • Information about resource waits, by running DBCC SQLPERF(WAITSTATS).
    • The current running SQL Server Batch for connections that are blocked by others or blocking others, by running the DBCC INPUTBUFFER statement.
    • The ending time, according to the computer that is running"

    Good luck..

    phil

  • May 24, 2004 at 7:39 pm

    #507563

    I use this sp that I found on this site.  I've found it very useful.

    http://vyaskn.tripod.com/fn_get_sql.htm

    It shows the actual code that's running.

  • May 25, 2004 at 4:13 am

    #507601

    Thanks so much I am using the first SP already I haven't seen the one form Angela though thanks alot!!!

     

    Laura

Viewing 5 posts - 1 through 4 (of 4 total)

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