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 < 2) > 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.