When to create statistics and when to update statistics.?

Problem: When to create statistics and when to update statistics.?

Solution:

When to Create Statistics:

SQL Server Query Optimizer by default creates statistics for indexes on tables or indexed views. If the index is a filtered index it creates filtered statistics. Also when the AUTO_CREATE_STATISTICS ON the optimizer creates statistics. For the most part the default statistics give a high efficient query cost plan by the optimizer. In some cases, we can improve query plans by adding additional statistics. For example, filtered statistics on a subset of data rows or multicolumn statistics on query predicate columns might improve the query plan.

  and below are the scenarios:

·         In most cases columns being used in JOIN, WHERE, ORDER BY, or GROUP clauses are good candidate to have up-to-date statistics on them.

·         The Database Engine Tuning Advisor suggests creating statistics.

·         The query predicate contains multiple correlated columns that are not already in the same index.

·         The query selects from a subset of data.

·         The query has missing statistics.

It’s recommended to keep the AUTO_CREATE_STATISTICS option ON so the query optimizer continues to routinely create single-column statistics for query predicate columns. It’s also recommended to keep the cardinality when making multiple column statistics as this affects density on the statistics.

When to Update Statistics:

Even though we have AUTO_UPDATE_STATISTICS option ON, the query optimizer determines when the statistics are out of date and then update them, sometimes the statistics gets stale by data changes after the last statistics update has occurred. but in meantime when you want to improve the query plans and execution costs by updating statistics using UPDATE STATISTICS statement or the stored procedure sp_updatestats. When you update statistics the queries get recompiled. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application.

To determine when statistics were last updated, use the STATS_DATE function.
Consider updating statistics for the following conditions:

·         Query execution times are slow.

·         Insert operations occur on ascending or descending key columns.

·         After maintenance operations.

The identification of stale statistics is done by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold as mentioned below.

• A database table with no rows gets a row
• A database table had fewer than 500 rows when statistics was last created or updated and is increased by another 500 or more rows
• A database table had more than 500 rows when statistics was last created or updated and is increased by 500 rows + 20 percent of the number of rows in the table when statistics was last created or updated.

Example 1:

You can find when each statistics object of a database table was updated using the below query, I am using a property function STATS_DATE which needs two arguments to be passed (object_id of indexed view or table name, index_id). This can be helpful to know whether the statistics are stale on the table:

SELECT i.name AS index_name,  

    STATS_DATE(i.object_id, index_id) AS statistics_update_date ,

                t.name as table_name

FROM sys.indexes   i

INNER JOIN sys.tables t

ON t.object_id = i.OBJECT_ID

WHERE t.is_ms_shipped=0

and t.name ='Orders'

OUTPUT:

index_name	statistics_update_date	table_name
PK__MobileUn__44F5ECB5B4A1E1C9	2016-11-03 10:12:29.973	Orders