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??
LVL 12
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Open in new window

sachitjainAuthor Commented:
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.
at the moment it is happening you can find it with this
	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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
sachitjainAuthor Commented:
No jogos this object is not even a trigger. I specifically checked list of triggers and their object ids. Any other possibility?
object-name(1232059475)  won't return anything?
Could it be that you create objects and drop them again?
sachitjainAuthor Commented:
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?
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.
sachitjainAuthor Commented:
So in that case blocking process should hold the object_id of stored procedure that holds the temp table or dynamically generated temp table?
Surrely not the Id of the procedure, if not you would have identified the object_id.
I think it's the id of a temp table but that the locking occurs because it's involved with other tables.

If it's happening more often, try to monitor it when it's happening, example will start at 23:00  and will check for blockers each 15 seconds and will repeat that 500 times


WAITFOR TIME '23:00:00'

   -- If there are blockers...
   IF EXISTS(SELECT 1 FROM sys.sysprocesses WHERE 
      blocked != 0) 
      -- put identify blocker and blocked here and save for inspection
      -- use script I earlier posted for that  

WAITFOR DELAY '00:00:15'
GO 500

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sachitjainAuthor Commented:
Thanks jogos, I would try that and tell you the results.
sachitjainAuthor Commented:
Thanks for the help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.