Again another interesting article from RED GATE …

SQL Server’s statistics provide the query optimizer with the information it needs to retrieve table data as efficiently as possible. If the statistics aren’t correct, query performance will be unnecessarily slow.

So how do you check if your statistics are up to date?

One option is to turn on AUTO_UPDATE_STATISTICS, which updates them when the number of row changes in a table reaches 20% of its total rows.

However, this threshold is too high for large tables. For example, a table with 1 million rows will only update statistics after 200,000 changes. Such a high limit can affect query plans and performance.

To change this behavior, you can enable trace flag 2371, which makes the threshold that triggers the AUTO_UPDATE_STATISTICS dynamic.

Once a table reaches 25,000 rows, the required number of updates starts to decrease. A table with 1 million rows, for example, only needs around 3.2% changes to trigger AUTO_UPDATE_STATISTICS.

If you enable this trace flag, it’s still important to identify whether the AUTO_UPDATE_STATISTICS are running often enough. If they’re not, you’ll need to schedule the update using a SQL Agent job.

You can find when the last statistics update happened, and how many row modifications have taken place since, using the sys.dm_db_stats_properties DMV with a few JOINs (download the scripts as a .zip):

SELECT    [schema]

,   [Table]

,   name

,   [Statistic]

,   [last_updated]

,   [rows]

,   [rows_sampled]

,   [Modifications]

,   ( 100 * sp.modification_counter ) / sp.[rows] [Percent]   — Calculates the percent of modifications

FROM      [sys].[stats] AS   INNER JOIN sys.stats_columns sc ON s.stats_id = sc.stats_id                              AND s.object_id = sc.object_id  INNER JOIN sys.columns c ON c.object_id = sc.object_id                              AND c.column_id = sc.column_id  INNER JOIN sys.objects o ON s.object_id = o.object_id  INNER JOIN sys.schemas sch ON o.schema_id = sch.schema_id  OUTER APPLY sys.dm_db_stats_properties              (.[object_id], .[stats_id])  AS [sp]WHERE   sch.name <> ‘sys’        AND ( sp.modification_counter > 1000              OR ( sp.modification_counter * 100 ) > sp.rows            )   — removes rows with too few modifications

ORDER BY [Percent] DESC ,        last_updated DESC — the result in order of priority

To make this easier, let’s create a user defined function, StatisticsModifications, which retrieves information about all the statistics in the database:

CREATE FUNCTION StatisticsModifications ( )RETURNS TABLEASRETURN   

( SELECT    [schema]

,                [Table]

,                name

,                [Statistic]

,                [last_updated]

,                [rows]

,                [rows_sampled]

,                [Modifications]

FROM        [sys].[stats] AS   INNER JOIN sys.stats_columns sc ON s.stats_id = sc.stats_id                             AND s.object_id = sc.object_id  INNER JOIN sys.columns c ON c.object_id = sc.object_id                             AND c.column_id = sc.column_id  INNER JOIN sys.objects o ON s.object_id = o.object_id  INNER JOIN sys.schemas sch ON o.schema_id = sch.schema_id  OUTER APPLY sys.dm_db_stats_properties(.[object_id],                                      .[stats_id]) AS [sp]    WHERE     sch.name <> ‘sys’    )

Now you can use this function (with some filters) to retrieve the most outdated statistics.

SELECT  [schema]

,        [Table]

,        name

,        [Statistic]

,        [last_updated]

,        [rows]

,        [rows_sampled]

,        [Modifications]

,        ( 100 * modifications ) / [rows] [Percent]FROM    dbo.statisticsmodifications()/* removes rows with too few modifications */WHERE   ( modifications > 1000          OR ( modifications * 100 ) > [rows]        )

ORDER BY [Percent] DESC,  last_updated DESC

With the results of this query, you can identify if your statistics are being updated often enough, or if you need to schedule some extra statistics.