If you experience a resource problem while running sp_iqcheckdb, you may see one the following messages in the sp_iqcheckdb output or in the .iqmsg file:
Out
of memory and DBCC Out
of Memory Errors You do not have enough
memory for this operation. You may need to prevent other IQ operations
or other applications from running concurrently with the sp_iqcheckdb stored
procedure.
No buffers available and DBCC
Out of Buffers Errors The DBA may
need to increase the buffer cache size.
Buffer cache sizes are set permanently using the database option TEMP_CACHE_MEMORY_MB. The server startup switches -iqmc and -iqtc can be used to override the buffer cache size values set using the database options. See the section “Setting buffer cache sizes” in Chapter 4, “Managing System Resources” of the Performance and Tuning Guide for information on using both the database options and the server startup switches to set buffer cache sizes.
You should not run multiple database consistency checks at the same time, as DBCC is optimized to run one instance.
The CPU utilization of DBCC can be limited by specifying the sp_iqcheckdb parameter resources resource-percent, which controls the number of threads with respect to the number of CPUs. The default value of resource-percent is 100, which creates one thread per CPU and should match the load capacity of most machines. Set resource-percent to a value less than 100 to reduce the number of threads, if you are running DBCC as a background process. The minimum number of threads is 1.
If resource-percent > 100, then there are more threads than CPUs, which may increase performance for some machine configurations.
The database option DBCC_PINNABLE_CACHE_PERCENT can be used to tune DBCC buffer usage. The default of DBCC_PINNABLE_CACHE_PERCENT is to use 50% of cache. For more information, see “DBCC_PINNABLE_CACHE_PERCENT option” in Chapter 2, “Database Options,” in Reference: Statements and Options.
