Why SQL Server Statistics Matter

By:


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

p0118-01

which produces an output similar to this:

p0118-02

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

p0118-03

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

Here is the output:

p0118-04

p0118-05

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

p0119-01

The output is

p0119-02

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

p0119-03

Leave a Reply