asked on

SQL Server Blocked Process Report

Hi Experts

I profiled one SQL Server db with 'Blocked Process Report' event during one load test and observed so much of blocking happening on to the database. Attached is TextData value of one such blocking event which is in XML format. I can understand BlockedProcess and BlockingProcess XML elements in this attached XML and under of both of these elements I also find one inputbuf element that contains object ids. Now in attached xml, object id under blocked process is 752057765, however under blocking process is 1232059475. Now I could figure out the object corresponding to object id 752057765 which is a stored procedure but there is no object corresponding to object id 1232059475. Could anybody please tell what kind of object is it and how could I fetch its object name??
BlockedProcessReport.xml

jogos

SELECT name, object_id, type_desc
FROM sys.objects
WHERE object_id= object_id

Open in new window

sachitjain

ASKER

Thanks jogos but I already know it. As I stated earlier, there is one inputbuf element each under blockedprocess element and blockingprocess element which contains object ids. Moroever object id it has under blocked process element's inputbuf element is 752057765 and that under blocking process element's inputbuf element is 1232059475. Through your query or object_name sysfunction, I could figure out the object corresponding to object id 752057765 which is a stored procedure but there is no object corresponding to object id 1232059475. So my doubt is regarding this particular object id.

jogos

at the moment it is happening you can find it with this

SELECT
	Blocking.session_id as BlockingSessionId
	, Sess.login_name AS BlockingUser
	, BlockingSQL.text AS BlockingSQL
	, Waits.wait_type WhyBlocked
	, Blocked.session_id AS BlockedSessionId
	, USER_NAME(Blocked.user_id) AS BlockedUser
	, BlockedSQL.text AS BlockedSQL
	, DB_NAME(Blocked.database_id) AS DatabaseName
FROM sys.dm_exec_connections AS Blocking
INNER JOIN sys.dm_exec_requests AS Blocked
	ON Blocking.session_id = Blocked.blocking_session_id
INNER JOIN sys.dm_os_waiting_tasks AS Waits
	ON Blocked.session_id = Waits.session_id
RIGHT OUTER JOIN sys.dm_exec_sessions Sess
	ON Blocking.session_id = sess.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS BlockingSQL
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL
ORDER BY BlockingSessionId, BlockedSessionId

Open in new window

Could be a trigger: sys.triggers

jogos

To make it more clear as a valuable answer:
Triggers are not stored in sys.objects so it could be that your object is a trigger and then you can find it in sys.triggers

sachitjain

ASKER

No jogos this object is not even a trigger. I specifically checked list of triggers and their object ids. Any other possibility?

jogos

object-name(1232059475) won't return anything?

Could it be that you create objects and drop them again?

sachitjain

ASKER

No jogos it does not return anything. That was the first thing that I checked.

I could see some table variables and temp tables getting created while course of execution. But could they cause blocking?

jogos

It can especially with a insert into # then you can lock the catalog tables, but expect not to have the object_id of the # table.

sachitjain

ASKER

So in that case blocking process should hold the object_id of stored procedure that holds the temp table or dynamically generated temp table?

ASKER CERTIFIED SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sachitjain