Parallel Statistics Update

Recently in my own practice, I was abruptly reminded of the value of SQL Server's Update Statistics with Full Scan: to make a long story short, we had a system that was getting a bad query plan that would consistently peg one of the CPUs for hours at a time. If multiple requests for the same data came in, more CPUs would be taken up, eventually using 100% of all processors. The query was a simple one, and we were stumped by this issue for a time. It turned out that the maintenance on the database in question just had too low a sampling rate for Update Statistics, which had the effect of causing the optimizer to choose a bad plan. Changing to Update Statistics with Full Scan solved the issue.

However, full scan statistics update can be slow and expensive on a large system, and a single update statistics job for a whole database can easily exceed an overnight maintenance window. This gave me the idea to create jobs that could update statistics on the system in parallel, as follows.

Note: this technique is specifically designed for a manual statistics update, and might not be appropriate on all systems. For example, if you already reindex regularly, then the statistics for the reindexed objects may automatically be updated, and there is no need to update statistics separately.

The idea here is to make all subsystems work on your server to update as many statistics objects as possible, within a defined maintenance window, in order from the worst cases to the best. The hope is that the process can run nightly, and will chip away at all the indexes and statistics objects in order. If the maintenance window runs out, it'll stop and not interfere with the server's performance during the day. When the next evening rolls around, it will begin again, but will work on the worst cases first, which hopefully will include any objects for which there was not time to update statistics the previous night. On a large system, the statistics should at least be updated every few nights, in a round-robin fashion.

The design consists of a collection of SQL Agent jobs: one is a "controller" that starts and stops the process, and the others are "workers" that perform the updates against collections of indexes. The controller establishes a queue of objects to be updated, and the workers pull items from that queue until they run out or the controller stops the process at the end of the maintenance time period.

Establish a Set of Indexes to Update

To implement this pattern, we begin with a select statement that contains all the indexes and statistics objects needed, with data about their status and size:

select
schemas.name as table_schema,
tbls.name as table_name,
i.name as index_name,
i.id as table_id,
i.indid as index_id,
i.groupid,
i.rowmodctr as modifiedRows,
(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) as rowcnt,
stats_date( i.id, i.indid ) as lastStatsUpdate,
'False' as Processed
into ##updateStatsQueue
from sysindexes i
inner join sysobjects tbls on i.id = tbls.id
inner join sysusers schemas on tbls.uid = schemas.uid
inner join information_schema.tables tl
on tbls.name = tl.table_name
and schemas.name = tl.table_schema
and tl.table_type='BASE TABLE'
where 0 < i.indid and i.indid < 255
and table_schema <> 'sys'
and i.rowmodctr <> 0
and (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid <> 0

This statement will query mainly the sysindexes table to create a list of all indexes and statistics objects, for non-system tables, with each object's last statistics update time, the number of rows in the underlying table, and the number of rows modified since the last statistics update. The results are restricted only to those objects that have changed since the last statistics update, by choosing only rows where sysindexes.rowmodcounter is nonzero. (Note that this value can be negative in some cases, so I am deliberately using "<> 0.")

There is one tricky bit to this: objects that are strictly statistics -- as opposed to indexes -- do appear in sysindexes, but always with a row count (sysindexes.rowcnt) of zero; sysindexes does not report the row count in the underlying table. In order to prioritize the queue I am generating, I would like to be able to compute how many modified rows there are out of the total number of rows in the underlying table, that is rowmodctr / rowcnt. However, we'd get either nonsense or a divide by zero error for objects that are statistics-only, since rowcnt is always zero. For that reason, I have this subquery:

select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid <>

This will transform the row count for the statistics objects into the row count for the underlying table.

Use the Set of Indexes to Control Multiple Worker Jobs

After the select statement runs, we have a global temporary table containing a list of indexes to update. It's important that the table have global scope, because the controller job is going to need to share it with the worker jobs actually performing the updates. Next, I "wrap" this select with some other code to control how long this process is allowed to run, and to start and stop our "worker" jobs, and put the code into a SQL Agent job:

declare @starttime datetime;
set @starttime = getdate();

-- Number of minutes to continue updating stats
-- Routine will complete its current operation and exit
-- if more than this timespan has elapsed:
declare @maxDur decimal (5,2);
set @maxdur = 480;

print 'Updating statistics in ' + DB_NAME();
print '';

set nocount on;

-- Make the queue of indexes to update:

if object_id('tempdb..##updateStatsQueue') is not null drop table ##updateStatsQueue;

select
schemas.name as table_schema,
tbls.name as table_name,
i.name as index_name,
i.id as table_id,
i.indid as index_id,
i.groupid,
i.rowmodctr as modifiedRows,
(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) as rowcnt,
stats_date( i.id, i.indid ) as lastStatsUpdate,
'False' as Processed
into ##updateStatsQueue
from sysindexes i
inner join sysobjects tbls on i.id = tbls.id
inner join sysusers schemas on tbls.uid = schemas.uid
inner join information_schema.tables tl
on tbls.name = tl.table_name
and schemas.name = tl.table_schema
and tl.table_type='BASE TABLE'
where 0 < i.indid and i.indid < 255
and table_schema <> 'sys'
and i.rowmodctr <> 0
and (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid <> 0;

-- Start worker jobs to process the queue

print 'Starting worker jobs';

-- Change the job names below as needed for implementation:
exec msdb..sp_start_job @job_name = 'Update Stats Worker 1';
exec msdb..sp_start_job @job_name = 'Update Stats Worker 2';
exec msdb..sp_start_job @job_name = 'Update Stats Worker 3';
exec msdb..sp_start_job @job_name = 'Update Stats Worker 4';

-- Monitor the queue; remove its temp table and exit once the entire table
-- has been processed or the max duration has elapsed:

while ( datediff( minute, @starttime, getdate() ) < @maxdur
and exists( select 1 from ##updateStatsQueue where processed = 'False' ) )
begin
-- Pause while worker jobs update stats
print 'Working ' + cast( getdate() as varchar(50) );
waitfor delay '000:02:00';
end

drop table ##updateStatsQueue;

print '';
print 'Statistics update stopped/ended ' + cast( getdate() as varchar(50) );

This will create the queue as a global temp table, then start four other jobs that will perform the work. When either the maximum duration has elapsed or the workers have processed the whole queue, it will drop the temp table, which will cause the worker jobs to stop and exit.

Code the Worker Jobs to Prioritize Updates

Next, I create four worker jobs, each with code similar to the following. The worker job works in a loop: it will first "claim" the top item from the queue table according to criteria we can customize. In this case I am mainly looking at objects where the number of modifications since the last stats update is a high percentage of the total number of rows in the underlying table. It will then compose a dynamic SQL statement that updates the statistics, after which it repeats until either all the objects in the queue have been processed, or the controller job has dropped the queue table, indicating that work should cease:

-- Process statistics objects from queue ##updateStatsQueue
-- until it has been completely processed or no longer exists

print 'Updating statistics in ' + DB_NAME()
print ''

declare @curtable sysname;
declare @curschema sysname;
declare @curindex sysname;
declare @curmodrows int;
declare @currows bigint;
declare @sql varchar(2000);

-- Loop until we run out of stats objects or the queue is dropped
while ( object_id('tempdb..##updateStatsQueue') is not null )
begin

-- Get the top stats object to work on, and remove its entry from the queue

update ##updateStatsQueue
set @curschema = table_schema = q.table_schema,
@curtable = table_name = q.table_name,
@curindex = index_name = q.index_name,
@currows = rowcnt = q.rowcnt,
@curmodrows = modifiedRows = q.modifiedRows,
processed = 'True'
from ##updateStatsQueue q
inner join ( select top 1 *
from ##updateStatsQueue q3
where processed = 'False'

-- Note: You may wish to add criteria or change the order clause here to
-- tune the ordering of statistics updates for your specific server:
-- For example: "and groupid = (x)" or "and table_name like '[a-g]%'"
-- The ordering given is from tables with a large percentage modified rows
-- to tables with a small percentage modified rows (preventing divide by zero)

order by abs(modifiedrows)/( cast( rowcnt as decimal ) + 0.01 ) desc, lastStatsUpdate
) q2
on q.table_schema = q2.table_schema
and q.table_name = q2.table_name
and q.index_name = q2.index_name;

-- If the update statement found no matching rows, then our work is done; exit:
if @@ROWCOUNT = 0 break;

else
begin
print cast( getdate() as varchar(100) );
print 'Updating stats for '
+ '[' + @curschema + '].[' + @curtable + '].[' + @curindex + ']'
+ '(' + cast( @curmodrows as varchar(1000) ) + ' modifications against '
+ cast( @currows as varchar(1000) ) + ' rows.)';

set @sql = 'update statistics '
+ '[' + @curschema + '].[' + @curtable + '] [' + @curindex + ']'
+ ' with fullscan '

exec( @sql );

end
end

print ''
Print 'Statistics update stopped/ended ' + cast( getdate() as varchar(50) );

In this case the only fancy bit is the syntax of the update statement against the queue table: it sets local variables for the name of the object that the worker will be updating and marks the row in the queue table so other workers will not use it, all in one statement, to prevent contention for or deadlocks on the queue table.

Schedule the Jobs

Finally, these jobs need a schedule. Only the "controller" job should be executed on a schedule; it will start the worker jobs after the queue table is created, and the workers will stop on their own when they have no more queue entries to process. For this reason, I have a note in the description of the worker jobs to the effect, "This job is deliberately unscheduled and is invoked by job 'x'." (I hope that will prevent a well-intentioned teammate from adding a schedule to the worker jobs, which would not technically cause problems but could be confusing.) I also set the Agent jobs to write output to a log file, so that I can review the output of the Print statements in the jobs and ensure that everything is running as expected.

Note that the controller job has a "maximum duration" variable that can be adjusted to fit the maintenance window for a system. No new statistics updates will be started after that time has elapsed, but worker jobs that are in the middle of an update will run it to completion before they stop. A long-running update that starts right at the end of the maintenance window will keep running, so take that into account when setting your schedule. Note that the largest tables are not necessarily processed first, the most heavily modified tables are, so there is likely to be a large table at the end of the list. This is even more true in that, percentage-wise, a large table might be less likely to have a large percentage of modified rows. For best results time the controller job to stop before the end of your maintenance window, by some interval that will allow the workers to complete the update they are currently executing.

Notes on Performance and Older Versions of SQL Server

Its important to note that this procedure, right out of the box, might not perform better than a "single-threaded" version on all systems, without some tweaks. If you consider implementing this, be sure to think through these issues:

• Does your system have fast enough storage infrastructure and enough memory so that parallel statistics updates will better utilize the hardware than serial updates? It could be that contention for disk or memory makes this solution less optimal.
• Would it help to alter the ordering of updates, for example to isolate objects from one filegroup using one worker and from another filegroup, on different disks, using another worker?
• Are multiple workers updating statistics on the same underlying tables at the same time? I believe that SQL Server will serialize statistics updates from different connections that hit the same table, so simultaneous updates might be faster or might be slower depending on the details of how it does that, and what advantage there is from having the table in cache as opposed to re-fetching it from disk.

If you are running a version of SQL Server 2000 SP3a or earlier, there is a known issue where concurrent statistics updates against the same underlying table can deadlock on the sysindexes table. This will have the effect that some of the worker jobs will be killed as a result of deadlock detection, leaving just one (one worker cannot deadlock against itself). For this case, consider SP4 or change the code that the worker jobs use to select from the queue to make them pick indexes on mutually exclusive tables (for example, select for tables named like '[a-g]%' for one worker and '[h-m]%' for the next, and so on. See http://support.microsoft.com/kb/826754.