Rick Caminiti dotCOM

Like all other query optimizers, SQL uses statistics to create query plans that improve query performance. The optimizer generally generates the necessary statistics for a high quality query plan, however, in a few cases, you need to create additional statistics or modify the query design for “optimal” results.

Query Optimization Statistics are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view. The query optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan. For example, the query optimizer could use cardinality estimates to choose the index seek operator instead of the more resource-intensive index scan operator, and in doing so improve query performance.

You can create statistics objects with filters to improve query performance for queries that select from well-defined subsets of data. They use a filter predicate to select the subset of data that is included in the statistics. Well-designed filtered statistics can improve the query execution plan compared with full-table statistics.

Syntax

CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ ,...n ] )
  [ WHERE <filter_predicate> ]
  [ WITH
  [ [ FULLSCAN
   | SAMPLE number { PERCENT | ROWS }
   | STATS_STREAM = stats_stream ] [ , ] ]
  [ NORECOMPUTE ]
] ;

<filter_predicate> ::=
  <conjunct> [AND ]

<conjunct> ::=
  <disjunct> | <comparison>

<disjunct> ::=
  column_name IN (constant ,…)

<comparison> ::=
  column_name <comparison_op> constant

<comparison_op> ::=
  IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

SAMPLES

USE AdventureWorks;
GO
CREATE STATISTICS ContactMail1
  ON Person.Contact (ContactID, EmailAddress)
  WITH SAMPLE 5 PERCENT;

OR

IF EXISTS (SELECT name FROM sys.stats
  WHERE name = N'ContactPromotion1'
  AND object_id = OBJECT_ID(N'Person.Contact'))
DROP STATISTICS Person.Contact.ContactPromotion1
GO
CREATE STATISTICS ContactPromotion1
  ON Person.Contact (ContactID, EmailAddress, EmailPromotion)
WHERE EmailPromotion = 2
WITH SAMPLE 50 PERCENT;
GO


For more information, see http://msdn.microsoft.com/en-us/library/ms188038.aspx

For information about when to CREATE STATISTICS, see http://msdn.microsoft.com/en-us/library/ms188038.aspx