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.
]]>