Share via

Create Multi-Column Statistics

  • Article

When a query has a multi-column condition, consider creating multi-column statistics if you suspect that the optimizer is not producing the best plan for the query. You get multi-column statistics as a by-product of creating a multi-column index, so if there is already a multi-column index that supports the multi-column condition, there is no need to create statistics explicitly. Auto create statistics only creates single-column statistics, never multi-column statistics. So if you need multi-column statistics, create them manually, or create a multi-column index.

Consider a query that accesses the AdventureWorks.Person.Contact table, and contains the following condition:

FirstName = 'Catherine' AND LastName = 'Abel'

To make selectivity estimation more accurate for this query, create the following statistics object:

CREATE STATISTICS LastFirst ON Person.Contact(LastName,FirstName)

This statistics object will be useful for queries that contain predicates on LastName and FirstName, as well as LastName alone. In general, the selectivity of a predicate on any prefix of the set of columns in a multi-column statistics object can be estimated using that statistics object.

For a statistics object to fully support a multi-column condition, a prefix of the columns in the multi-column statistics object must contain the columns in the condition. For example, a multi-column statistics object on columns (a,b,c) only partially supports the condition a=1 AND c=1; the histogram will be used to estimate the selectivity for a=1, but the density information for c will not be used since b is missing from the condition. Multi-column statistics on (a,c) or (a,c,b) would support the condition a=1 AND c=1, and density information could be used to improve selectivity estimation.

Comments

  • Anonymous
    October 22, 2007
    If we restore a SQL Server 2000 database in SQL 2005, will these stats automatically get created for the composite indexes? Or do we need to recreate the composite index for the composite stats to be created?