Update statistics on a 4 billion record table

Question

On a SQL Server 2016 SP3 database, we have several (under 10) tables with billions of records.

It's a 24/7 system with limited maintenance windows so it's essential that user impact is limited while updating statistics.

What would be the proper/safe way to get statistics updated using Ola Hallengren's scripts?

When started, can it be paused / stopped?

Answer 1

Optimising statistics can be a daunting task, depending on the design of the table and how data is entered/modified/deleted. I can only supply some general ideas on how your statistics could be maintained.

Option 1 - SQL Server Automated (default)

If the data in the tables is evenly distributed and you have statements querying the data evenly amongst all records, then you could leave the statistics update to SQL Server itself.

Statistics are automatically updated according to the following formula:

MIN ( 500 + (0.20 * n), SQRT(1,000 * n) )

n: number of rows in table

^{Reference: Statistics (Microsoft Learn | SQL Server)}

If you were to have a table with a billion rows, then SQL Server will calculate the following.

500 + (0.20 * 1,000,000,000) = 200,000,500

and

SQRT(1,000 * 1,000,000,000) = 1,000,000

and the MIN() value of the two results is:

1,000,000

SQL Server will automatically recalculate the statistics after 1,000,000 rows have been modified in a table that is 1 billion rows in size.

If the query optimizer decides that the statistics are outdated and should be updated before a query is executed and a query execution plan compiled, then the statistics can be updated too.

When the automatic update statistics option, AUTO_UPDATE_STATISTICS is ON, the Query Optimizer determines when statistics might be out-of-date and then updates them when they are used by a query. This action is also known as statistics recompilation. Statistics become out-of-date after modifications from insert, update, delete, or merge operations change the data distribution in the table or indexed view. The Query Optimizer determines when statistics might be out-of-date by counting the number of row modifications since the last statistics update and comparing the number of row modifications to a threshold. The threshold is based on the table cardinality, which can be defined as the number of rows in the table or indexed view.

This could and might occur in any case. That is, with or without you or the users realizing something is happening in the background.

^{Reference: Statistics (Microsoft Learn | SQL Server)}

Option 2 - Updates Statistics with OLA Script

If the data is not evenly inserted and/or you have indexes on date columns where users will generally query the newest data according to the date, then you might have to update your statistics with OLA's scripts or a solution of your choice periodically.

This parameters for the percentage of data that has changed before the statistics are updated is something you will have to manually determine with some trial and errors.

In my example I wanted all the statistics to updated after 8 % of the data had changed, so I used the following parameters:

EXECUTE [dbo].[IndexOptimize]
@Databases = 'YOURDATABASE',
@Indexes = 'ALL_INDEXES,-YOURDATABASE.dbo.Table1,-YOURDATABASE.dbo.Table2,-YOURDATABASE.dbo.Table3,-YOURDATABASE.dbo.Table4,-YOURDATABASE.dbo.Table5',
@UpdateStatistics = 'ALL',
@StatisticsModificationLevel = 8,
@LogToTable = 'Y'

However, the tables Table1 - Table5 were excluded from this update, because I wanted them to be updated after 2 % of data had changed. This was because the data is these main tables (700 Mio, 60 Mio, 42 Mio, 21 Mio and 350,000 rows) was updated regularly and if the statistics became stale, then performance would drop after a day of modifications.

Those five tables were updated with the following parameters:

EXECUTE [dbo].[IndexOptimize]
@Databases = 'YOURDATABASE',
@Indexes = 'YOURDATABASE.dbo.Table1,YOURDATABASE.dbo.Table2,YOURDATABASE.dbo.Table3,YOURDATABASE.dbo.Table4,YOURDATABASE.dbo.Table5',
@UpdateStatistics = 'ALL',
@StatisticsModificationLevel = 2,
@StatisticsSample=100,
@LogToTable = 'Y'

^{Reference: SQL Server Index and Statistics Maintenance (Ola Hallengren | Maintenance Script Documentation)}

Based on your requirements...

...you might not want to sample 100% of the data in order to stay inside your maintenance window. (@StatisticsSample parameter). You could consider using a lower value for the @StatisticsSample parameter.

StatisticsSample Indicate, as a percentage, how much of a table is gathered when updating statistics. A value of 100 is equivalent to a full scan. If no value is specified, then SQL Server automatically computes the required sample.

The StatisticsSample option in IndexOptimize uses the SAMPLE and FULLSCAN options in the SQL Server UPDATE STATISTICS command.

^{Reference: SQL Server Index and Statistics Maintenance (Ola Hallengren | Maintenance Script Documentation)}

Option 3 - Manual Adhoc Updates

If you are really into hardcore, hands-on DBA work, then you could query the statistics for a given table and show if the statistics would be automatically updated according to the trigger value with the following script:

SELECT 'DBCC SHOW_STATISTICS ([' + [sch].[NAME] + '.' +[so].[NAME] + '] , [' + [ss].[NAME] + ']) WITH STAT_HEADER' AS [SHOW_STATS],
       'update statistics ' + [sch].[name] + '.' + [so].[name] + ' ' + [ss].[name] + ' WITH FULLSCAN', -- ROWCOUNT=100, PAGECOUNT=100 | FULLSCAN | WITH ROWCOUNT=100, PAGECOUNT=100
       'DBCC UPDATEUSAGE(' + DB_NAME() + ', ''' + [sch].[NAME] + '.' +[so].[NAME] + ''') WITH COUNT_ROWS',
       [sch].[name] + '.' + [so].[name] AS [TableName],
       [ss].[name] AS [Statistic],
       [sp].[last_updated] AS [StatsLastUpdated],
       [sp].[rows] AS [RowsInTable],
       [sp].[rows_sampled] AS [RowsSampled],
       [sp].[modification_counter] AS [RowModifications],
       100 / (1.0 * [sp].[rows]) * [sp].[modification_counter] AS [PercentChanged],
       SQRT(1000 * [sp].[rows]) AS [2012 Algorithm Change Value],
       CASE 
            WHEN SQRT(1000 * [sp].[rows]) < [sp].[modification_counter] THEN 1
            ELSE 0
       END AS [Auto Update 2012 Triggered],
       [sp].[rows] * 1.0 / 100 * 20 + 500 AS [2008 Algorithm Change Value],
       CASE 
            WHEN [sp].[rows] * 1.0 / 100 * 20 + 500 < [sp].[modification_counter] THEN 1
            ELSE 0
       END [Auto Update 2008 Triggered]
FROM   [sys].[stats] [ss]
       JOIN [sys].[objects] [so]
            ON  [ss].[object_id] = [so].[object_id]
       JOIN [sys].[schemas] [sch]
            ON  [so].[schema_id] = [sch].[schema_id]
       OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id], [ss].[stats_id]) sp
WHERE  1 = 1
       AND [so].[type] = 'U'                                               -- Only User tables
       AND [sp].[modification_counter] > 0                                 -- data has been modified in the stats properties
       -- AND 100/(1.0*[sp].[rows])*[sp].[modification_counter] < 10.0     -- maximum percentage change (certain tables have a high volatility)
       AND 100/(1.0*[sp].[rows])*[sp].[modification_counter] > 1.00        -- minimum percentrage change (we aren't going to be looking at statistics with a very low percnentag of change)
       AND [sp].[rows] > 1000000                                           -- only look at statistics which contain more than 1'000'000 rows.
       -- AND [sp].[last_updated] > dateadd(hh,-1,getdate())               -- only look at statistics that have been updated more than x hours ago
       -- AND [sp].[last_updated] > dateadd(mi,-45,getdate())              -- only look at statistics that have been modified more than x minutes ago
       -- AND [sch].[name] = 'dbo'                                          -- only tables from this schema
       -- AND [so].[name] in ('table1','table2','table3','table4','table5') --involved tables
       AND [ss].[name] NOT LIKE '_WA_Sys%'                                  -- no automatically created statistics
       AND [ss].[name] not like '_dta_stat%'                                -- no Database Tuning Advisor statistics (DTA)
       -- AND (SQRT(1000 * [sp].[rows]) < [sp].[modification_counter] OR [sp].[rows] * 1.0 / 100 * 20 + 500 < [sp].[modification_counter])
ORDER BY
       [sch].[name] + '.' + [so].[name] ASC,
       [ss].[name] ASC,
       [sp].[last_updated] DESC;

This will generate an output for the statistics with more than a million rows and a modification level of more than 1%. It omits results for _WTA (automatically created) statistics and for _dta.... (Database Tuning Advisor) statistics. It also shows you if the statistics would be updated according to the trigger values in the different SQL Server versions. The WHERE clause contains some general predicates to give you an idea what you can search for.

If the statistics wouldn't be updated according to the trigger value, then you could run the UPDATE STATICS... statement that is generated for that specific statistic/table.

Based on your requirements...

...you might not want to sample 100% of the data in order to stay inside your maintenance window. You would have to modify the line in the statement for the UPDATE STATISTICS part and change the WITH FULLSCAN option to the WITH SAMPLE nn option. Additionally adding the MAXDOP = n setting would allow the update statistics to run in parallel, but only on n processors.

e.g.

...
    'update statistics ' + [sch].[name] + '.' + [so].[name] + ' ' + [ss].[name] + ' WITH SAMPLE 10, MAXDOP = 8',
...

^{Reference: UPDATE STATISTICS (Transact-SQL) (Microsoft Learn | SQL Server)}

The sampling values and MAXDOP settings are something you might have to determine in a test environment. Or start with low values and see how things work out.

Expert Mode / Further Investigation

If you are interested in the Statistics Histogram (explained in the linked MS article) , then you run the generated DBCC SHOW_STATISTICS... command for those statistics which is created with the statement.

The other DBCC UPDATEUSAGE statement that is created can be used to tweak statistics if you know how to do it.

Good luck.