Database Reference
In-Depth Information
sqlserver.is_system = 0
)
)
add target
package0.event_file
(
set filename = 'c:\ExtEvents\Expensive Queries.xel'
)
with
(
event_retention_mode=allow_single_event_loss
,max_dispatch_latency=15 seconds
);
■
It is very important to find the right threshold values that define expensive queries in your system.
even though you do not want to capture an excessive amount of information, it is important to collect the
right
information.
Optimization of relatively inexpensive, but very frequently executed queries, can provide much better results when
compared to optimization of expensive, but rarely executed queries. analysis of the
sys.dm_exec_query_stats
view
data can help you detect some of those queries, and it should be used in parallel with extended events.
Important
Listing 28-26 shows the query that extracts the data from the
event_file
target.
Listing 28-26.
Extracting expensive queries information
;with TargetData(Data, File_Name, File_Offset)
as
(
select convert(xml,event_data) as Data, file_name, file_offset
from
sys.fn_xe_file_target_read_file('c:\extevents\Expensive*.xel'
,null, null, null)
)
,EventInfo([Event], [Event Time], [CPU Time], [Duration], [Logical Reads]
,[Physical Reads], [Writes], [Rows], [Statement], [PlanHandle]
,File_Name, File_Offset)
as
(
select
Data.value('/event[1]/@name','sysname') as [Event]
,Data.value('/event[1]/@timestamp','datetime') as [Event Time]
,Data.value('((/event[1]/data[@name="cpu_time"]/value/text())[1])'
,'bigint') as [CPU Time]
,Data.value('((/event[1]/data[@name="duration"]/value/text())[1])'
,'bigint') as [Duration]
,Data.value('((/event[1]/data[@name="logical_reads"]/value/text())[1])'
,'int') as [Logical Reads]
,Data.value('((/event[1]/data[@name="physical_reads"]/value/text())[1])'
,'int') as [Physical Reads]
