How to find why query was blocked?

Novitskiy Denis 0
Viewed 57

I have some query, that run from client and have 5 sec timeout. In most cases query work faster then 1 second, but sometimes it got timeout after 5 sec and the query execution does not start. I added a message at the beginning of the procedure that it is running, but even that is not saved in the database. I ran a profiler trace and this is what it shows I also turned on the blocked process report with paramert 4 sec, but i don`t see any blocking processes in that time (17:29-17:30). How to investigate this problem?

sql-server
1 Answers

One way of finding and analyzing blocking queries is using Extended Events. first of all run this script:

EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE
EXEC sp_configure 'blocked process threshold (s)', 3
RECONFIGURE
EXEC sp_configure 'Show Advanced Options', 0
RECONFIGURE

N.B. – I’ve set the threshold limit here to 3 seconds. Depending on your environment you may wish to change it. Then create an extended event to capture blocking information :

CREATE EVENT SESSION [BlockedProcesses] ON SERVER 
    ADD EVENT sqlserver.blocked_process_report
    ADD TARGET package0.event_file(SET filename=N'C:\SQLServer\XEvents\BlockedProcesses\BlockedProcesses.xel',max_file_size=(128))  
WITH (STARTUP_STATE=OFF)
GO
-- Don't forget to change the file path

Then start the session:

ALTER EVENT SESSION [BlockedProcesses] ON SERVER
    STATE = START;

Now that the session is running you can run this query in order to analyze blocking queries:

SELECT
 [XML DATA] AS [Raw XML],
 [XML Data].value('(/event[@name=''blocked_process_report'']/@timestamp)[1]','DATETIME') AS [TimeStamp],
 [XML Data].value('(/event/data[@name=''database_name'']/value)[1]','SYSNAME') AS [Database Name],
 [XML Data].value('(/event/action[@name=''username'']/value)[1]','SYSNAME') AS [Username],
 [XML Data].value('(/event/data[@name=''transaction_id'']/value)[1]','BIGINT') AS [Transaction ID],
 [XML Data].value('(/event/data[@name=''lock_mode'']/text)[1]','SYSNAME') AS [Lock Mode],
 [XML Data].value('(/event/data[@name=''duration'']/value)[1]','BIGINT')/1024 AS [Duration (ms)],
 [XML Data].value('(/event/data[@name=''blocked_process'']/value/blocked-process-report/blocked-process/process/inputbuf)[1]','SYSNAME') AS [Blocked Query],
 [XML Data].value('(/event/data[@name=''blocked_process'']/value/blocked-process-report/blocked-process/process/@waitresource)[1]','SYSNAME') AS [Wait Resource], 
 [XML Data].value('(/event/data[@name=''blocked_process'']/value/blocked-process-report/blocking-process/process/inputbuf)[1]','SYSNAME') AS [Blocking Query]
FROM
    (SELECT OBJECT_NAME   AS [Event], 
  CONVERT(XML, event_data) AS [XML Data]
FROM 
   sys.fn_xe_file_target_read_file('C:\SQLServer\XEvents\BlockedProcesses\BlockedProcesses*.xel',NULL,NULL,NULL)) AS v
ORDER BY [XML Data].value('(/event[@name=''blocked_process_report'']/@timestamp)[1]','DATETIME') DESC

Collecting this data with Extended Events means that you won’t have to sit at your desk, running queries, and waiting for blocking occur.

Ehsan HP0
Related