How to find out which query blocking other

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

You Might find these useful:
http://www.sql-server-performance.com/blocking.asp
And
http://www.sql-server-performance.com/reducing_locks.asp

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

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.

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

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

Madhu

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

madhu

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.