The following parameters affect dbcc checkstorage:
max worker processes – set this parameter with sp_dbcc_updateconfig. It updates the value of max worker processes in the dbcc_config table for each target database.
number of worker processes – set this configuration parameter with sp_configure. It updates the server_name.cfg file.
memory per worker process – set this configuration parameter with sp_configure. It updates the server_name.cfg file.
After changing the value of the sp_configure parameters, you must restart Adaptive Server for the change to take effect. For details, see Chapter 4, “Setting Configuration Parameters.”
max worker processes specifies the maximum number of worker processes used by dbcc checkstorage for each target database, while number of worker processes specifies the total number of worker processes supported by Adaptive Server. Worker processes are not dedicated to running dbcc checkstorage operations.
Set the value for number of worker processes high enough to allow for the number of processes specified by max worker processes. A low number of worker processes reduces the performance and resource consumption of dbcc checkstorage. dbcc checkstorage will not use more processes than the number of database devices used by the database. Cache size, CPU performance, and device sizes might suggest a lower worker processes count. If there are not enough worker processes configured for Adaptive Server, dbcc checkstorage will not run.
maximum parallel degree and maximum scan parallel degree have no effect on the parallel functions of dbcc checkstorage. When maximum parallel degree is set to 1, parallelism in dbcc checkstorage is not disabled.
dbcc checkstorage requires multiple processes, so number of worker processes must be set to at least 1 to allow for a parent process and a worker process.
sp_plan_dbccdb recommends values for the number of worker processes, depending on database size, number of devices, and other factors. You can use smaller values to limit the load on your system. dbcc checkstorage may use fewer worker processes than sp_plan_dbccdb recommends or fewer than you configure.
Using more worker processes does not guarantee faster performance. The following scenario describes the effects of two different configurations:
An 8GB database has 4GB of data on disk A and 0.5GB of data on each of the disks B, C, D, E, F, G, H, and I.
With 9 worker processes active, the time it takes to run dbcc checkstorage is 2 hours, which is the time it takes to check disk A. Each of the other 8 worker processes finishes in 15 minutes and waits for the disk A worker process to finish.
With 2 worker processes active, the time it takes to run dbcc checkstorage is still 2 hours. The first worker process processes disk A and the other worker process processes disks B, C, D, E, F, G, H, and I. In this case, there is no waiting, and resources are used more efficiently.
memory per worker process specifies the total memory allocation for worker processes support in Adaptive Server. The default value is adequate for dbcc checkstorage.
