Analyzing Query Performance - SQL Server Query Performance Tuning - page 86

Costly Queries with a Single Execution
You can identify the costly queries by analyzing a session output file or by querying sys.dm_exec_query_stats. For
this example, we'll start with identifying queries that perform a large number of logical reads, so you should sort the
session output on the logical_reads data column. You can change that around to sort on duration or CPU, or even
combine them in interesting ways. You can access the session information by following these steps:

1. Capture a session that contains a typical workload.

2. Save the session output to a file.

3. Open the file by using File ➤ Open and select a .xel file to use the data browser window.

Sort the information there.

Alternatively, you can query the trace file for analysis sorting by the logical_reads field. 4.

WITH xEvents
AS (SELECT object_name AS xEventName,
CAST (event_data AS XML) AS xEventData
FROM sys.fn_xe_file_target_read_file('C:\Sessions\QueryPerformanceMetrics*.xel',
NULL, NULL, NULL)
)
SELECT xEventName,
xEventData.value('(/event/data[@name=''duration'']/value)[1]',
'bigint') Duration,
xEventData.value('(/event/data[@name=''physical_reads'']/value)[1]',
'bigint') PhysicalReads,
xEventData.value('(/event/data[@name=''logical_reads'']/value)[1]',
'bigint') LogicalReads,
xEventData.value('(/event/data[@name=''cpu_time'']/value)[1]',
'bigint') CpuTime,
CASE xEventName
WHEN 'sql_batch_completed'
THEN xEventData.value('(/event/data[@name=''batch_text'']/value)[1]',
'varchar(max)')
WHEN 'rpc_completed'
THEN xEventData.value('(/event/data[@name=''statement'']/value)[1]',
'varchar(max)')
END AS SQLText,
xEventData.value('(/event/data[@name=''query_hash'']/value)[1]',
'binary(8)') QueryHash
INTO Session_Table
FROM xEvents;

SELECT st.xEventName,
st.Duration,
st.PhysicalReads,
st.LogicalReads,
st.CpuTime,
st.SQLText,
st.QueryHash
FROM Session_Table AS st
ORDER BY st.LogicalReads DESC;