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

The INCLUDE statement specifies the non-key columns to be added to the leaf level of the nonclustered index. The nonclustered index can be unique or non-unique.
Column names cannot be repeated in the INCLUDE list and cannot be used simultaneously as both key and non-key columns. Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table.

All data types are allowed except text, ntext, and image. The index must be created or rebuilt offline (ONLINE = OFF) if any one of the specified non-key columns are varchar(max), nvarchar(max), or varbinary(max) data types.

Computed columns that are deterministic and either precise or imprecise can be included columns. Computed columns derived from image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml data types can be included in non-key columns as long as the computed column data types is allowable as an included column.

SYNTAX:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
  ON ( column [ ASC | DESC ] [ ,...n ] )
  [ INCLUDE ( column_name [ ,...n ] ) ]


SAMPLE:
Lets say you have an ETL Job for your fact table that has lookups to validate all of the dimensional keys and get the surrogate keys. You may have something like

SELECT productID, EDWproductID
FROM EDWProduct
WHERE productID = ?

OR

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N’98000′ and N’99999′;

It’s obvious that you’ll need indexes on the key fields ProductID and PostalCode, but what might not have been obvious is that it makes sense to include the non-key values into the index to improve performance. Granted, you don’t want to make a habit of creating indexes to meet every single query’s needs, but if these queries are being used in ETL jobs multiple times every day or are being used in reports every day etc, it would make sense to create these indexes to avoid touching the table at all. If these columns are included in the index, you read the index only and the query engine has no need to read the table.


CREATE NONCLUSTERED INDEX IX_Address_PostalCode
  ON Person.Address (PostalCode)
  INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

CREATE NONCLUSTERED INDEX IX_EDWProduct
  ON EDWProduct (ProductID)
  INCLUDE (EDWProductID);
GO

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