Database Reference
In-Depth Information
Avoid Use of No_Event_Loss
Extended Events is set up such that some events will be lost. It's extremely likely, by design. But, you can use a setting,
No_Event_Loss, when configuring your session. If you do this on systems that are already under load, you may see a
significant additional load placed on the system since you're effectively telling it to retain information in the buffer
regardless of consequences. For small and focused sessions that are targeting a particular behavior, this approach can
be acceptable.
Other Methods for Query Performance Metrics
Setting up an Extended Event session allows you to collect a lot of data for later use, but the collection can be a little
bit expensive. In addition, you have to wait on the results, and then you have a lot of data to deal with. If you need
to immediately capture performance metrics about your system, especially as they pertain to query performance,
then the dynamic management views sys.dm_exec_query_stats for queries and sys.dm_exec_procedure_stats for
stored procedures are what you need. If you still need a historical tracking of when queries were run and their
individual costs, an Extended Events session is still the best tool. But if you just need to know, at this moment, the
longest-running queries or the most physical reads, then you can get that information from these two dynamic
management objects. But, the data in these objects are dependent on the query plan remaining in the cache.
If the plan ages out of cache, this data just goes away. The sys.dm_exec_query_stats DMO will return results for all
queries, including stored procedures, but the sys.dm_exec_procedure_stats will return information only for
stored procedures.
Since both these DMOs are just views, you can simply query against them and get information about the
statistics of queries in the plan cache on the server. Table
6-5
shows some of the data returned from the
sys.dm_exec_query_stats DMO.
Table 6-5.
sys.dm_exec_query_stats Output
Column
Description
Plan_handle
Pointer that refers to the execution plan
Creation_time
Time that the plan was created
Last_execution time
Last time the plan was used by a query
Execution_count
Number of times the plan has been used
Total_worker_time
Total CPU time used by the plan since it was created
Total_logical_reads
Total number of reads used since the plan was created
Total_logical_writes
Total number of writes used since the plan was created
Query_hash
A binary hash that can be used to identify queries with similar logic
Query_plan_hash
A binary hash that can be used to identify plans with similar logic
Table
6-5
is just a sampling. For complete details, see Books Online.
