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) );