Database updating statistics

Now if your query uses Name and Salary without using Age, the density is not available for cardinality estimates.

database updating statistics-74

SQL Server Query Optimizer creates the histogram by sorting the column values, computing the number of values that match each distinct column value and then aggregating the column values into a maximum of 200 contiguous histogram steps.

Each histogram step includes a range of column values followed by an upper bound column value, which includes all possible column values between boundary values (excluding the boundary values themselves).

Statistics refers to the statistical information about the distribution of values in one or more columns of a table or an index.

The SQL Server Query Optimizer uses this statistical information to estimate the cardinality, or number of rows, in the query result to be returned, which enables the SQL Server Query Optimizer to create a high-quality query execution plan.

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.

Often statistics are created on a single column but it’s not uncommon to create statistics on multiple columns.

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.

For example, a statistic created on these columns and in order - Name, Age, and Salary.

In this case, the statistics object will have densities for the following column prefixes: (Name), (Name, Age), and (Name, Age, Salary).

Now if you re-execute the above same queries, you will notice two things.

Tags: , ,