SQL Server Performance

How to find out which query blocking other

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by NewDBA, Jan 26, 2007.

  1. NewDBA New Member

    Hi<br /><br />All I can see running<br /><br />dbcc inputbuffer (&lt;id&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br />is this statement:<br /><br /> sp_prepexec;1<br /><br />is there a way I can get more info what exactly t-sql is causing blocking ?<br /><br />Maybe I can query some systables to get this info?<br /><br />Does anyone has script?<br /><br />thank you
  2. Luis Martin Moderator

    How about sp_who2?




    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. Luis Martin Moderator

    Or,

    USE master
    GO
    CREATE PROCEDURE sp_leadblocker
    AS
    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!'




    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  4. ranjitjain New Member

  5. madhuottapalam New Member

    IN sql server 2005 you have DMV sys.dm_tran_locks to find the blocks. In BOL it is described with a query.

    Copied From BOL :

    The following query will show blocking information.

    SELECT
    t1.resource_type,
    t1.resource_database_id,
    t1.resource_associated_entity_id,
    t1.request_mode,
    t1.request_session_id,
    t2.blocking_session_id
    FROM sys.dm_tran_locks as t1
    INNER JOIN sys.dm_os_waiting_tasks as t2
    ON t1.lock_owner_address = t2.resource_address;


    or

    you can also use Activity Monitor

    Madhu
  6. NewDBA New Member

    thank you madhuottapalam, I did find that article about 2005<br /> - and that actually that why I was wandering maybe I can <br />get that info in SQL 2000 somehow. <br /><br /><img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />I guess the answer is no.<br /><br />Have to use 2005.
  7. NewDBA New Member

    Actually if I open profiler and filter by object name=sp_prepexec
    and SPID - blocking speed -

    I can see T-Sql statement - that is blocking others.


    That means that there should be a way to create query that can retreive it from the database

    Still - maybe someone has the script?

    Once again - during blocking I can't see much in current activities.

    Often there is general system procedures like sp_prepexec instead of actual T-SQL query that causing blocking.

    Is there a way to retrieve that T-SQL statement by quering system tables?

    thank you
  8. madhuottapalam New Member

    i think these all are queried from sysprocesses system table. Read about this table in BOL

    Madhu
  9. madhuottapalam New Member

    find out the blocking spid from sysprocesses and use dbcc inputbuffer (spid) to get the query which was ran by that spid.

    madhu
  10. MohammedU New Member

    Here is actual blocking script used by MS guys in their troubleshooting... but there is no way you can get the exact query information when it is executed by sp_prepexec and sp_execsql statement... you have to correlate the time and spid with sql trace/profiler...

    How to monitor blocking in SQL Server 2005 and in SQL Server
    http://support.microsoft.com/kb/271509/

    How to troubleshoot SQL Server performance issues
    http://support.microsoft.com/kb/298475/

    Mohammed U.

Share This Page