Database Workload Optimization - SQL Server Query Performance Tuning - page 508

Unfortunately, using SSMS provides limited ways of analyzing the Extended Events output. For instance, consider
a query that is executed frequently. Instead of looking at the cost of only the individual execution of the query, you
should also try to determine the cumulative cost of repeatedly executing the query within a fixed period of time.
Although the individual execution of the query may not be that costly, the query may be executed so many times that
even a little optimization may make a big difference. SSMS is not powerful enough to help analyze the workload in
such advanced ways. So, while you can group by the batch_text column, the differences in parameter values mean
you'll see different groupings of the same stored procedure call. If all your queries were stored procedures, you could
capture the object_id and then group on that. But most systems have at least some ad hoc queries, if not a lot ad hoc
queries, so that may not be workable. For in-depth analysis of the workload, you must import the content of the trace
file into a database table. The output from the session puts most of the important data into an XML field, so you'll
want to query it as you load the data as follows:
IF (SELECT OBJECT_ID('dbo.ExEvents')
) IS NOT NULL
DROP TABLE dbo.ExEvents;
GO
WITH xEvents
AS (SELECT object_name AS xEventName,
CAST (event_data AS XML) AS xEventData
FROM sys.fn_xe_file_target_read_file('C:\Data\MSSQL11.RANDORI\MSSQL\Log\
QueryMetrics*.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_plan_hash'']/value)[1]',
'binary(8)') QueryPlanHash
INTO dbo.ExEvents
FROM xEvents;
You need to substitute your own path and file name for <ExEventsFileName> . Once you have the content in a
table, you can use SQL queries to analyze the workload. For example, to find the slowest queries, you can execute this
SQL query:
SELECT *
FROM dbo.ExEvents AS ee
ORDER BY ee.Duration DESC;