{"id":1889,"date":"2020-11-16T07:00:00","date_gmt":"2020-11-16T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=1889"},"modified":"2024-10-14T12:46:58","modified_gmt":"2024-10-14T09:46:58","slug":"pos-syllegoyme-blocking-queries-meso-extended-event-kai-pos-diav","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-syllegoyme-blocking-queries-meso-extended-event-kai-pos-diav\/","title":{"rendered":"How we collect blocking queries via Extended Event and how we read its data"},"content":{"rendered":"

In older article<\/a> we had seen how we create Extended Event <\/strong>to see long-running queries. In this article we will build Extended Event that records the blockings<\/strong>.<\/p>\n\n\n\n

All we need to do is create it with a T-SQL command. After it is created, it will record in the path that we have declared to it in an XML file all the queries that lasted more than 1 second.<\/p>\n\n\n\n

To create the Extended Event<\/h4>\n\n\n\n

We must not forget to change the path to the one where we want the data to be stored.<\/p>\n\n\n\n

Then we execute it in a simple query window.<\/p>\n\n\n\n

CREATE EVENT SESSION [BlockedProcessReport] ON SERVER \nADD EVENT sqlserver.blocked_process_report \nADD TARGET package0.event_file(SET filename=N'D:\\Xevents\\blockings.xel',max_file_size=(20),max_rollover_files=(50))\nWITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)\nGO\n\nalter event session BlockedProcessReport on server state=start;\ngo<\/pre>\n\n\n\n
But for it to work, we must have changed the parameter blocked process threshold<\/a> which defines how long blockings will be reported by alerts, profilers and extended events related to blocking. Setting its value to 10 it will only record blockings that lasted longer than 10 seconds<\/strong>. With the default value of 0 it will not record anything. Accepts values from 5 seconds and above.<\/p>\n\n\n\n
To apply this change, we run the following in a query window on the instance:<\/p>\n\n\n\n
EXEC sp_configure 'show advanced options', 1;\nRECONFIGURE WITH OVERRIDE;\nEXEC sp_configure 'blocked process threshold (s)', 10;\nRECONFIGURE WITH OVERRIDE;<\/pre>\n\n\n\n
How do we read the results?<\/h4>\n\n\n\n
In the path we defined during its creation XEvent <\/strong>a .xel and a .xem file were created. Through the dynamic view sys.fn_xe_file_target_read_file<\/strong> with parameters these files we result in all the XML <\/strong>file with the information.<\/p>\n\n\n\n
But since it is impractical to read an XML by eye, the file must somehow be filtered into a more readable form.<\/p>\n\n\n\n
So I sat down and made a query that does this job.<\/p>\n\n\n\n
For starters, through the name we created, Xevent finds the path that is dynamically located and defines it as a parameter in the query.<\/p>\n\n\n\n
Then we define the XML as one Common Table Expression<\/strong>. By doing SELECT we can get the value from each property we need.<\/p>\n\n\n\n
In the where statement we can filter specific bases, the duration and the period of time it was executed.<\/p>\n\n\n\n
This part needs attention, as the default xml timestamp is in UTC timezone, so to put the actual time that the server has, we add the time difference that the server has with the UTC timezone (DATEADD(HOUR,DATEDIFF(hour, SYSUTCDATETIME(),SYSDATETIME() )<\/em>).<\/p>\n\n\n\n
declare\n\n@xel as varchar(max),\n@xem as varchar(max) \n\nset @xel=(select \n--n.name as XeventName,\nSUBSTRING(cast(f.value as varchar(max)),1,len(cast(f.value as varchar(max)))-4)+'*.xel' as xel\n--,SUBSTRING(cast(f.value as varchar(max)),1,len(cast(f.value as varchar(max)))-4)+'*.xem' as xem\nfrom sys.server_event_session_fields f\ninner join sys.server_event_sessions n on f.event_session_id = n.event_session_id\nWHERE f.NAME = 'filename' \nand n.name like '%BlockedProcessReport%') --Set xevent Name\n\n\nset @xem=(select \n--n.name as XeventName,\n--SUBSTRING(cast(f.value as varchar(max)),1,len(cast(f.value as varchar(max)))-4)+'*.xel' as xel\nSUBSTRING(cast(f.value as varchar(max)),1,len(cast(f.value as varchar(max)))-4)+'*.xem' as xem\nfrom sys.server_event_session_fields f\ninner join sys.server_event_sessions n on f.event_session_id = n.event_session_id\nWHERE f.NAME = 'filename' \nand n.name like '%BlockedProcessReport%')  --Set xevent Name\n\n\n;WITH XEvents AS\n(\nselect object_name, CAST(event_data AS XML) AS A FROM sys.fn_xe_file_target_read_file(\n@xel\n,@xem\n, NULL, NULL)\n)\nSELECT \nd.name as DB_Name,\nA.value ('(\/event\/action[@name=''database_name'']\/value)[1]', 'VARCHAR(MAX)') AS DB_Name,\nDATEADD(HOUR,DATEDIFF(hour,  SYSUTCDATETIME(),SYSDATETIME() ),A.value ('(\/event\/@timestamp)[1]', 'DATETIME')) AS [Time],\nA.value ('(event\/data[@name=\"duration\"]\/value)[1]','bigint') \/ 1000000  AS [Duration_Seconds],\nA.value ('(event\/data[@name=\"object_id\"]\/value)[1]','bigint')   AS Object_ID,\nA.value ('(\/event\/data[@name=\"lock_mode\"]\/text)[1]',  'VARCHAR(MAX)') AS lock_mode,\nA.value ('(\/event\/data[@name=\"blocked_process\"]\/value\/blocked-process-report\/blocked-process\/process\/inputbuf)[1]',  'VARCHAR(MAX)') AS BlockedQuery,\n--A.value ('(\/event\/data[@name=\"blocked_process\"]\/value\/blocked-process-report\/blocked-process\/process\/@hostname)[1]',  'VARCHAR(MAX)') AS BlockedHostName,\nA.value ('(\/event\/data[@name=\"blocked_process\"]\/value\/blocked-process-report\/blocked-process\/process\/@loginname)[1]',  'VARCHAR(MAX)') AS BlockedLoginName,\nA.value ('(\/event\/data[@name=\"blocked_process\"]\/value\/blocked-process-report\/blocking-process\/process\/inputbuf)[1]',  'VARCHAR(MAX)')  AS BlockingQuery,\nA.value ('(\/event\/data[@name=\"blocked_process\"]\/value\/blocked-process-report\/blocking-process\/process\/inputbuf)[1]',  'VARCHAR(MAX)')  AS BlockingQuery,\nA.value ('(\/event\/data[@name=\"blocked_process\"]\/value\/blocked-process-report\/blocking-process\/process\/@loginname)[1]',  'VARCHAR(MAX)')  AS BlockingLoginName,\nA.value ('(\/event\/data[@name=\"blocked_process\"]\/value\/blocked-process-report\/blocking-process\/process\/@hostname)[1]',  'VARCHAR(MAX)')  AS BlockingLoginName,\nA.value ('(\/event\/data[@name=\"blocked_process\"]\/value\/blocked-process-report\/blocking-process\/process\/executionStack\/frame\/@sqlhandle)[1]',  'VARCHAR(MAX)') AS sql_handle1,\nsf1.text as full_text1,\nqp1.query_plan as query_plan1,\nA   AS xml_report\n--,A.value('(event\/data[@name=\"cpu_time\"]\/value)[1]','bigint') \/1000000 AS cpu_seconds\n--,A.value('(event\/data[@name=\"physical_reads\"]\/value)[1]','bigint') AS physical_reads\n--,A.value('(event\/data[@name=\"logical_reads\"]\/value)[1]','bigint') AS logical_reads\n--,A.value('(event\/data[@name=\"row_count\"]\/value)[1]','bigint') AS row_count\nFrom XEvents x\ninner join sys.databases d on x.A.value ('(\/event\/data[@name=\"database_id\"]\/value)[1]',  'VARCHAR(MAX)') =d.database_id\nouter apply sys.dm_exec_sql_text (CONVERT(VARBINARY(MAX), x.A.value ('(\/event\/data[@name=\"blocked_process\"]\/value\/blocked-process-report\/blocking-process\/process\/executionStack\/frame\/@sqlhandle)[1]',  'varchar(max)'), 1)) as sf1\nouter apply sys.dm_exec_query_plan((select top 1 plan_handle from sys.dm_exec_query_stats where sql_handle = CONVERT(VARBINARY(MAX), x.A.value ('(\/event\/data[@name=\"blocked_process\"]\/value\/blocked-process-report\/blocking-process\/process\/executionStack\/frame\/@sqlhandle)[1]',  'varchar(max)'), 1)order by last_execution_time desc))as qp1\n\nwhere  1=1\nand DATEADD(HOUR,DATEDIFF(hour,  SYSUTCDATETIME(),SYSDATETIME() ),A.value ('(\/event\/@timestamp)[1]', 'DATETIME'))  between '2020-05-01 10:45:00.000' and '2020-05-30 12:45:00.000'\nand A.value ('(\/event\/action[@name=''database_name'']\/value)[1]', 'VARCHAR(MAX)') ='DB_1'<\/pre>\n\n\n\n
The result<\/h3>\n\n\n\n
The result of the query brought us for this period of time, on the basis we requested, the queries that were made block<\/strong>, from which query they were blocked, in which object and finally with which locking mode.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Sources:<\/h2>\n\n\n\n