Why SQL Server Statistics Matter

By: SolarWinds

To estimate the cost of an execution plan, the query optimizer needs to know, as precisely as possible, the number of records returned by a given query, and to help with this cardinality estimation, SQL Server uses and maintains optimizer statistics. Statistics contain statistical information describing the distribution of values in one or more columns of a table.

You can use the undocumented trace flags 9292 and 9204 to show information about the statistics loaded during the optimization process. Take a look at the query


which produces an output similar to this:


To better understand how it works, let’s create additional statistics objects:


Trace flag 9292 can be used to display the statistics objects that are considered interesting. Run the following query:

Here is the output:



Trace flag 9204 can be used to display the statistics objects that were used to produce a cardinality estimate. Test the following code:


The output is


To clean up, drop the statistics object you’ve just created:


Leave a Reply