{"id":1875,"date":"2020-09-16T07:00:00","date_gmt":"2020-09-16T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=1875"},"modified":"2025-04-30T13:29:51","modified_gmt":"2025-04-30T10:29:51","slug":"pos-syllegoyme-queries-me-megali-diarkeia-me","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-syllegoyme-queries-me-megali-diarkeia-me\/","title":{"rendered":"How do we collect long-running queries via Extended Event and how do we read its data"},"content":{"rendered":"

Many times we will need to find how many and which SQL queries were executed for a long time in SQL Server. The easiest solution with the least performance impact is to create a Extended Event<\/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 one second. <\/p>\n\n\n\n

To create the Extended Event<\/h2>\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 run it in a simple query window:<\/p>\n\n\n\n

CREATE EVENT SESSION [CaptureDuration] ON SERVER \nADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)\n    ACTION(package0.collect_cpu_cycle_time,sqlos.cpu_id,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.tsql_stack,sqlserver.username)\n    WHERE ([package0].[greater_than_equal_uint64]([duration],(10000000)))),\nADD EVENT sqlserver.sql_batch_completed(\n    ACTION(package0.collect_cpu_cycle_time,sqlos.cpu_id,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.tsql_stack,sqlserver.username)\n    WHERE ([package0].[greater_than_equal_uint64]([duration],(10000000)))),\nADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1)\n    ACTION(package0.collect_cpu_cycle_time,sqlos.cpu_id,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.tsql_stack,sqlserver.username)\n    WHERE ([package0].[greater_than_equal_int64]([duration],(10000000)))) \nADD TARGET package0.event_file(SET filename=N'D:\\Xevents\\longqueries_file.xel',max_file_size=(20),max_rollover_files=(50))\nWITH (MAX_MEMORY=4096 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 CaptureDuration on server state=start;\ngo\n<\/pre>\n\n\n\n
How we read the results<\/h2>\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 real time that the server has, we add the time difference that the server has with the UTC timezone which is done with functions<\/em> (DATEADD(HOUR,DATEDIFF(hour, SYSUTCDATETIME(),SYSDATETIME() )<\/em>):<\/p>\n\n\n\n
declare\n@xel as varchar(max),\n@xem as varchar(max) \n\nset @xel=(select \nSUBSTRING(cast(f.value as varchar(max)),1,len(cast(f.value as varchar(max)))-4)+'*.xel' as xel\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 '%CaptureDuration%') --Set xevent Name\n\nset @xem=(select \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 '%CaptureDuration%')  --Set xevent Name\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 \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\/action[@name=''sql_text'']\/value)[1]', 'VARCHAR(MAX)') AS sql_text,\nA.value ('(\/event\/action[@name=''client_hostname'']\/value)[1]', 'VARCHAR(MAX)') AS client_hostname,\nA.value ('(\/event\/action[@name=''client_app_name'']\/value)[1]', 'VARCHAR(MAX)') AS client_app_name,\nA.value ('(\/event\/action[@name=''nt_username'']\/value)[1]', 'VARCHAR(MAX)') AS nt_username,\nA.value ('(\/event\/action[@name=''username'']\/value)[1]', 'VARCHAR(MAX)') AS username,\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\nA   AS xml_report\nFrom XEvents\nwhere  1=1\n\nand DATEADD(HOUR,DATEDIFF(hour,  SYSUTCDATETIME(),SYSDATETIME() ),A.value ('(\/event\/@timestamp)[1]', 'DATETIME'))  between '2020-05-20 10:45:00.000' and '2020-05-22 12:45:00.000'\nand A.value ('(\/event\/action[@name=''database_name'']\/value)[1]', 'VARCHAR(MAX)') ='DBATools'\nand A.value('(event\/data[@name=\"duration\"]\/value)[1]','bigint') \/ 1000000  >= 15 --sec<\/pre>\n\n\n\n
The result<\/h2>\n\n\n\n
The result of the query brought us for this period of time to the database that we asked for the queries that were running and their duration:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Accordingly, to keep the blockings, we can follow the steps listed in this<\/a> the article. <\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n