{"id":736,"date":"2015-12-31T12:26:13","date_gmt":"2015-12-31T20:26:13","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=736"},"modified":"2017-04-13T09:19:30","modified_gmt":"2017-04-13T16:19:30","slug":"capture-blocking-information-with-extended-events-and-the-blocked-process-report","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/capture-blocking-information-with-extended-events-and-the-blocked-process-report\/","title":{"rendered":"Capture Blocking Information with Extended Events and the Blocked Process Report"},"content":{"rendered":"

I am a big fan of Adam Machanic’s<\/a> WhoIsActive script<\/a>, and when customers have issues with performance, it’s one of the first tools I recommend because it’s so simple to use and provides great information.\u00a0 Very often it helps with quickly determining an issue, but sometimes there’s a need to capture more information, particularly when locking and blocking is part of the issue.\u00a0 Adam’s script has an option to include blocking information, for example including the [blocking_session_id] column in the output and using @find_block_leaders = 1as an parameter.\u00a0 But sometimes you need more information, like the blocked process report.\u00a0 I’ve found one of the easiest ways to get that in SQL Server 2012 and higher is Extended Events.\u00a0 If you’re running SQL Server 2005 and higher, you can use Event Notifications<\/a> to capture the blocked process report.\u00a0 This option is nice because you are notified when the problem occurs.\u00a0 For those of you using SQL Server 2008R2 and below, you also have the option of capturing the blocked process report event through a server-side Trace<\/a>.\u00a0 But if you’re on SQL Server 2012 and higher, you can use Extended Events<\/a> and the blocked process report.\u00a0 Note: the blocked_process_report event does not exist in SQL Server 2008 or SQL Server 2008R2, which is why Trace is the method there.<\/em>\u00a0 The drawback to Extended Events is that you don’t get a notification that blocking occurred, but for those who are not as comfortable with Event Notifications – for whatever reason – Extended Events is a very simple alternative.<\/p>\n

The Setup<\/strong><\/p>\n

In order to capture a blocked process report, you must have the blocked process threshold system configuration option enabled.\u00a0 A good starting value is 15, which is the threshold in seconds at which the report is generated.\u00a0 To set this value, run the following code:<\/p>\n

\r\nEXECUTE sp_configure 'show advanced options', 1;\r\nGO\r\nRECONFIGURE;\r\nGO\r\nEXECUTE sp_configure 'blocked process threshold', 15;\r\nGO\r\nRECONFIGURE;\r\nGO\r\nEXECUTE sp_configure 'show advanced options', 0;\r\nGO\r\nRECONFIGURE;\r\nGO\r\n<\/pre>\n
The following code will create the event session and then start it.\u00a0 Note that you can create the event session and just have it defined in your system without running it.\u00a0 Then, if you start to have blocking you can set the blocked process threshold and start the event session.<\/p>\n
\r\n\/*\r\ncheck to see if the event session exists\r\n*\/\r\nIF EXISTS ( SELECT\u00a0 1\r\nFROM\u00a0\u00a0\u00a0 sys.server_event_sessions\r\nWHERE\u00a0\u00a0 name = 'Capture_BlockedProcessReport' )\r\nDROP EVENT SESSION [Capture_BlockedProcessReport] ON SERVER;\r\nGO\r\n\r\n\/*\r\ncreate the event session\r\nedit the filename entry if C:\\temp is not appropriate\r\n*\/\r\nCREATE EVENT SESSION [Capture_BlockedProcessReport]\r\nON SERVER\r\nADD EVENT sqlserver.blocked_process_report\r\nADD TARGET package0.event_file(\r\nSET filename=N'C:\\Temp\\Capture_BlockedProcessReport.xel'\r\n)\r\nWITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,\r\nMAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,\r\nTRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);\r\nGO\r\n\r\n\/*\r\nstart the event session\r\n*\/\r\nALTER EVENT SESSION [Capture_BlockedProcessReport]\r\nON SERVER\r\nSTATE = START;\r\nGO\r\n<\/pre>\n
Capturing Data
\n<\/strong><\/p>\n
Once the event session is started, then you just wait until the blocking occurs.\u00a0 The following code can be used to generate an example in your test\/dev environment:<\/p>\n
\r\n\/*\r\ncreate a table and insert\r\none row without committing\r\n*\/\r\nUSE [tempdb];\r\nGO\r\n\r\nCREATE TABLE [BlockingTest] (\r\n[ID] INT IDENTITY(1,1) PRIMARY KEY,\r\n[INFO] VARCHAR(10)\r\n);\r\nGO\r\n\r\nBEGIN TRANSACTION\r\nINSERT INTO [BlockingTest] ([INFO]) VALUES ('SQLskills');\r\nGO\r\n\r\n\/*\r\nrun the following statement in a different window\r\n*\/\r\nUSE [tempdb];\r\nGO\r\nSELECT *\r\nFROM [BlockingTest];\r\n\r\nGO\r\n<\/pre>\n
After about 15 seconds, run the following code back in the original window:<\/p>\n
\r\n\/*\r\nclean up (run in original window)\r\n*\/\r\nUSE [tempdb];\r\nGO\r\nCOMMIT;\r\nGO\r\nDROP TABLE [BlockingTest];\r\nGO\r\n<\/pre>\n
You can then stop the event session, and either leave it there until you need it again, or drop it entirely:<\/p>\n
\r\n\/*\r\nstop the event session\r\n*\/\r\nALTER EVENT SESSION [Capture_BlockedProcessReport]\r\nON SERVER\r\nSTATE = STOP;\r\nGO\r\n\r\n\/*\r\ndrop the event session\r\n*\/\r\nDROP EVENT SESSION [Capture_BlockedProcessReport]\r\nON SERVER;\r\nGO\r\n<\/pre>\n
Viewing the Report<\/strong><\/p>\n
To view the output from extended events you can open the .xel file in Management Studio or query the data using the sys.fn_xe_file_target_read_file function. I typically prefer the UI, but there’s currently no great way to copy the blocking report text and view it in the format you’re used to.\u00a0 But if you use the function to read and parse the XML from the file, you can…<\/p>\n
\r\nSELECT\r\n\u00a0\u00a0 \u00a0event_data.value('(event\/@name)[1]', 'varchar(50)') AS event_name,\r\n\u00a0\u00a0 \u00a0event_data.query('(event\/data[@name="blocked_process"]\/value\/blocked-process-report)[1]') as [blocked_process_report]\r\nFROM\r\n(\r\n\u00a0\u00a0 \u00a0SELECT CAST(event_data AS XML) AS event_data\r\n\u00a0\u00a0 \u00a0FROM sys.fn_xe_file_target_read_file('C:\\Temp\\Capture_BlockedProcessReport*.xel', NULL, NULL, NULL)\r\n) AS sub;\r\nGO\r\n<\/pre>\n
Depending on how long you let the blocking continue, you may have captured more than one event and therefore have multiple reports in the output:<\/p>\n
\"Retrieving<\/a>
Retrieving the blocked process report<\/figcaption><\/figure>\n
 <\/p>\n
You can click on the output for any row to see the blocked process in XML format, and then work through the blocking:<\/p>\n
\"The<\/a>
The blocked process report<\/figcaption><\/figure>\n
[Huge thanks to Jonathan for help with the XML.\u00a0 I don’t think XML and I will ever be friends.\u00a0 Geez.]<\/p>\n
Summary<\/strong><\/p>\n
If you’re in need of the blocked process report and running SQL Server 2012, you now have another option for getting that information.\u00a0 If you’re still new to extended events, check out the first two stairways in my XE series on SQLServerCentral<\/p>\n