Database Reference
In-Depth Information
FROM sys.dm_exec_query_stats AS deqs
GROUP BY deqs.query_plan_hash
) AS s
CROSS APPLY (SELECT plan_handle
FROM sys.dm_exec_query_stats AS deqs
WHERE s.query_plan_hash = deqs.query_plan_hash
) AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
ORDER BY TotalLogicalReads DESC;
This is so much easier than all the work required to gather session data that it makes you wonder why you
would ever use Extended Events at all. The main reason is precision. The sys.dm_exec_ query_stats view is a running
aggregate for the time that a given plan has been in memory. An Extended Events session, on the other hand, is a
historical track for whatever time frame you ran it in. You can even add session results to a database. With a list of data
that you can generate totals about the events in a more precise manner rather than simply relying on a given moment
in time. But understand that a lot of troubleshooting of performance problems is focused on what has happened
recently on the server, and since sys.dm_exec_query_stats is based in the cache, the DMV usually represents a recent
picture of the system, so sys.dm_exec_query_stats is extremely important. But, if you're dealing with that much more
tactical situation of what the heck is running slow right now, you would use sys.dm_exec_requests.
Identifying Slow-Running Queries
Because a user's experience is highly influenced by the response time of their requests, you should regularly monitor
the execution time of incoming SQL queries and find out the response time of slow-running queries, creating a query
performance baseline. If the response time (or duration) of slow-running queries becomes unacceptable, then you should
analyze the cause of performance degradation. Not every slow-performing query is caused by resource issues, though.
Other concerns such as blocking can also lead to slow query performance. Blocking is covered in detail in Chapter 12.
To identify slow-running queries, just change the queries against your session data to change what you're
ordering by, like this:
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,
xEventData.value('(/event/data[@name=''batch_text'']/value)[1]','varchar(max)') BatchText,
xEventData.value('(/event/data[@name=''statement'']/value)[1]','varchar(max)') StatementText,
xEventData.value('(/event/data[@name=''query_plan_hash'']/value)[1]','binary(8)') QueryPlanHash
FROM xEvents
ORDER BY Duration DESC;
For a slow-running system, you should note the duration of slow-running queries before and after the
optimization process. After you apply optimization techniques, you should then work out the overall effect on the
system. It is possible that your optimization steps may have adversely affected other queries, making them slower.
