Databases Reference
In-Depth Information
since the given SQL Server instance was started, they can also be used to get optimization
information for a specific query or workload, as we'll see in a moment.
Let us look at
sys.dm_exec_query_optimizer_info
first; as mentioned, you can
use this DMV to obtain statistics regarding the operation of the Query Optimizer, such as
how queries have been optimized, and how many of them have been optimized since the
instance started. This DMV returns three columns:
•
Counter
- the name of the optimizer event
•
Occurrence
- the number of occurrences of the optimization event for this counter
•
Value
- the average value per event occurrence.
38 counters were defined for SQL Server 2005, and a new one, called
merge
stmt
, was
added in SQL Server 2008, giving a total of 39.
To view the statistics for all the Query Optimizer events since the SQL Server instance
was started, we can just run:
SELECT
*
FROM
sys
.
dm_exec_query_optimizer_info
Listing 5-1.
Table 5-1 shows some example output from one of my SQL Server instances. It shows that
there have been 691,473 optimizations since the instance was started, that the average
elapsed time for each optimization was 0.0078 seconds, and that the average estimated
cost of each optimization, in internal cost units, was about 1.398. This particular example
shows optimizations of inexpensive queries, typical of an OLTP system.
