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
LVL 12
sachitjainAsked:
Who is Participating?
 
jogosConnect With a Mentor Commented:
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

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

WAITFOR TIME '23:00:00'
GO

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

   END
 
WAITFOR DELAY '00:00:15'
GO 500

Open in new window




0
 
jogosCommented:
SELECT name, object_id, type_desc
FROM sys.objects
WHERE object_id= object_id

Open in new window

0
 
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.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
jogosCommented:
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
0
 
jogosCommented:
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
0
 
sachitjainAuthor Commented:
No jogos this object is not even a trigger. I specifically checked list of triggers and their object ids. Any other possibility?
0
 
jogosCommented:
object-name(1232059475)  won't return anything?
 
Could it be that you create objects and drop them again?
0
 
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?
0
 
jogosCommented:
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.
0
 
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?
0
 
sachitjainAuthor Commented:
Thanks jogos, I would try that and tell you the results.
0
 
sachitjainAuthor Commented:
Thanks for the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.