SQL Server Query Optimizer uses statistics to estimate the distribution of values in one or more columns of a table or index views, and the number of rows (called ) to create a high-quality query execution plan.
Though the SQL Server Query Optimizer creates single column statistics when the AUTO_CREATE_STATISTICS database property is set to ON or when you create indexes on the table or views (statistics are created on the key columns of the indexes), there might be times when you need to create additional statistics using the CREATE STATISTICS command to capture cardinality, statistical correlations so that it enables the SQL Server Query Optimizer to create improved query plans.
When you find a query predicate containing multiple columns with cross column relationships and dependencies you should create multi-column statistics.
These multi-column statistics contain cross-column correlation statistics, often referred to as , to improve the cardinality estimates when query results depend on data relationships among multiple columns.
When creating multi-column statistics, be sure to put columns in the right order as this impacts the effectiveness of densities for making cardinality estimates.
Now let’s run these two queries and have a look on their execution plan.
Notice the yellow exclamation mark on the “Table Scan” operator; this indicates the missing statistics.
SQL Server Query Optimizer identifies these stale statistics before compiling a query and before executing a cached query plan.
The identification of stale statistics are done by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold as mentioned below.
The lowest of the sorted column values is the upper boundary value for the first histogram step.
Often columns being used in JOIN, WHERE, ORDER BY, or GROUP clauses are good candidate to have up-to-date statistics on them.
This actually helps SQL Server Query Optimizer to decide whether to use Index Seek or Index Scan.