Maintaining statistics is important for SQL Server performance . The SQL Server  query optimizer uses statistics to calculate \ estimate the number of rows (also known as cardinality) for a sql query . Cardinality influences the SQL Server query plan. A practical example , is the choice between an efficient Index Seek and an inefficient Index Scan.

Is your index missing statistics ?

The SQL Server query optimizer is a cost-based optimizer. Two factors determine the estimated cost:

1)The query plan has multiple levels processing rows. The total number of  rows is the plan cardinality

2)Operators used in the query defining the cost model of the algorithm

The cardinality is an input parameter for  the cost model. Maintaining cardinality information – will create improved estimates of the cost – which should lead to quicker query plans.

The Optimizer considers multiple execution plans – each execution plan has an associated cost. The Optimizer analyses the plans and decides which one to use. In a complex SQL Statements – which may have hundresds  of plans – the Optimizer defines a minimum possible cost – and chooses a plan close to the criteria.

Statistics are stored for tables in a statistics object. The object is either on a column list or an index. A statistics object is comprised of  :

1)Metadata header

2)Histogram with a value distribution in the first key column

3)Density vector for cross-column correlation

 The Engine computes cardinality with the data represented in the statistics object

 DBCC SHOW_STATISTICS  displays current query optimization statistics . A example output:

sys.dm_exec_query_optimizer_info – displays information about the SQL Server query optimizer. Use it for workload tuning process

It is possible to force the Optimizer to use a plan. Use USE PLAN to pass a query plan to the Optimizer. USE PLAN will force the plan to use and override the Optimizer. Typically used in an upgrade , where the later version of SQL Server optimizer does not return query plans as expected.

 Maintaining SQL Server statisticsis critical to accurate execution plans . If the data in the statistics objects is inaccurate the Optimizer will use an inefficient execution plan . DBCC SHOW_STATISTICS, sys.dm_exec_query_optimizer_info, and USE PLAN are three ways of exploring and maximising the Optimizer

Source:Jack Vamvas (http://www.sqlserver-dba.com)

Author: Jack Vamvas (http://www.sqlserver-dba.com)