Database Reference
In-Depth Information
Figure 12-15. Missing statistics indication in a graphical plan
in a database application, there is always the possibility of queries using columns with no indexes. therefore, in most
systems, for performance reasons, leaving the auto create statistics feature of SQl Server databases on is recommended.
Note
You can query the plans in cache to identify those plans that may have missing statistics.
SELECT dest.text AS query,
deqs.execution_count,
deqp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_text_query_plan(deqs.plan_handle,
deqs.statement_start_offset,
deqs.statement_end_offset) AS detqp
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE detqp.query_plan LIKE '%ColumnsWithNoStatistics%';
This query cheats just a little bit. I'm using a wildcard on both sides of a variable with the LIKE operator, which
is actually a common code issue (addressed in more detail in Chapter 18), but the alternative in this case is to run
an XQuery, which requires loading the XML parser. Depending on the amount of memory available to your system,
this approach, the wildcard search, can work a lot faster than querying the XML of the execution plan directly. Query
tuning isn't just about using a single method but understanding how they all fit together.
If you are in a situation where you need to disable the automatic creation of statistics, you may still want to track
where statistics may have been useful to your queries. You can use the Extended Events missing_column_statistics
to capture that information. For the previous examples, you can see an example of the output of this event in
Figure 12-16 .
 
 
Search WWH ::




Custom Search