Day-to-Day Service Plan - Part 2: Automatic Update of Statistics

In the previous post, automation of the process of defragmentation of indices was considered . Now it's the turn of statistics.

Actually what is it for?

When executing any query, the query optimizer, within the framework of the information it has, tries to build an optimal execution plan - which will display from itself a sequence of operations, due to which you can get the desired result described in the query.
')
In the process of choosing a particular operation, the query optimizer among the most important input data includes statistics describing the distribution of data values ​​for columns within a table or index.

This estimate of the number of elements allows the query optimizer to create more efficient execution plans. At the same time, if the statistics contain outdated data, less efficient operations may be selected that will lead to the creation of slow execution plans. For example, when for a small sample on outdated statistics, the more expensive Index Scan operator is selected instead of the Index Seek operator.

As you can see, to be as useful as possible to the query optimizer, the statistics must be accurate and fresh. From time to time, SQL Server periodically updates statistics itself — this behavior is governed by the AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS options.

In addition, when re-creating indexes, statistics on them is updated automatically with the FULLSCAN flag turned on, which guarantees the most accurate distribution of data. At reorganization of indexes the statistics is not updated.

When the data in the tables is changed very often, it is advisable to perform selective updating of statistics manually, using the UPDATE STATISTICS operation.

Also manual update is very important when the NORECOMPUTE flag is set for statistics, meaning that automatic updating of statistics is no longer required. You can view this property, as well as all the others, in the properties of statistics:

SELECT s.* FROM sys.stats s JOIN sys.objects o ON s.[object_id] = o.[object_id] WHERE o.is_ms_shipped = 0 

Using the capabilities of dynamic SQL, we will write a script to automatically update outdated statistics:

 DECLARE @DateNow DATETIME SELECT @DateNow = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = ( SELECT ' UPDATE STATISTICS [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] [' + s.name + '] WITH FULLSCAN' + CASE WHEN s.no_recompute = 1 THEN ', NORECOMPUTE' ELSE '' END + ';' FROM sys.stats s WITH(NOLOCK) JOIN sys.objects o WITH(NOLOCK) ON s.[object_id] = o.[object_id] WHERE o.[type] IN ('U', 'V') AND o.is_ms_shipped = 0 AND ISNULL(STATS_DATE(s.[object_id], s.stats_id), GETDATE()) <= @DateNow FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') PRINT @SQL EXEC sys.sp_executesql @SQL 

During execution, the following statements will be generated:

 UPDATE STATISTICS [Production].[Shift] [PK_Shift_ShiftID] WITH FULLSCAN; UPDATE STATISTICS [Production].[Shift] [AK_Shift_Name] WITH FULLSCAN, NORECOMPUTE; 

The criterion for the obsolescence of statistics in each specific situation can be different. In this example, 1 day.
In some cases, too frequent updating of statistics for large tables can significantly reduce database performance, so this script can be modified. For example, for large tables, update statistics less frequently:

 DECLARE @DateNow DATETIME SELECT @DateNow = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = ( SELECT ' UPDATE STATISTICS [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] [' + s.name + '] WITH FULLSCAN' + CASE WHEN s.no_recompute = 1 THEN ', NORECOMPUTE' ELSE '' END + ';' FROM ( SELECT [object_id] , name , stats_id , no_recompute , last_update = STATS_DATE([object_id], stats_id) FROM sys.stats WITH(NOLOCK) WHERE auto_created = 0 AND is_temporary = 0 -- 2012+ ) s JOIN sys.objects o WITH(NOLOCK) ON s.[object_id] = o.[object_id] JOIN ( SELECT p.[object_id] , p.index_id , total_pages = SUM(a.total_pages) FROM sys.partitions p WITH(NOLOCK) JOIN sys.allocation_units a WITH(NOLOCK) ON p.[partition_id] = a.container_id GROUP BY p.[object_id] , p.index_id ) p ON o.[object_id] = p.[object_id] AND p.index_id = s.stats_id WHERE o.[type] IN ('U', 'V') AND o.is_ms_shipped = 0 AND ( last_update IS NULL AND p.total_pages > 0 -- never updated and contains rows OR last_update <= DATEADD(dd, CASE WHEN p.total_pages > 4096 -- > 4 MB THEN -2 -- updated 3 days ago ELSE 0 END, @DateNow) ) FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') PRINT @SQL EXEC sys.sp_executesql @SQL 

In the next part, we will look at automating database backup.

If you want to share this article with an English-speaking audience, then please use the link to translate:
SQL Server Typical Maintenance Plans: Automatic Statistics Update