SQL Server Performance Forum – Threads Archive
How to find out which query blocking other
Hi<br /><br />All I can see running<br /><br />dbcc inputbuffer (<id><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
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.
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.
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.
]]>