Microsoft distributes Microsoft SQL Server 2008 R2, SQL Server 2008 R2 SP1, and SQL Server 2008 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included in the previous SQL Server 2008 R2, SQL Server 2008 R2 SP1, and SQL Server 2008 fix release.
Introduction
The DBCC CHECKDB command balances the need between performance and resources. Therefore, by default the DBCC CHECKDB command may not maximize resource usage when consistency checks for a database are run.
For example, the DBCC CHECKDB command may not maximize disk I/O usage when consistency checks for a database are run.
Resolution
Cumulative update information
SQL Server 2008 Service Pack 2
The fix for this issue was first released in Cumulative Update 9 for SQL Server 2008 Service Pack 2. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
2673382 Cumulative update package 9 for SQL Server 2008 Service Pack 2
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, see the following article in the Microsoft Knowledge Base:
2402659 The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 2 was released
Microsoft SQL Server 2008 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 2 hotfix to an installation of SQL Server 2008 Service Pack 2. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.
SQL Server 2008 Service Pack 3
The fix for this issue was first released in Cumulative Update 4 for SQL Server 2008 Service Pack 3. For more information about this cumulative update package, see the following article in the Microsoft Knowledge Base:
2673383 Cumulative update package 4 for SQL Server 2008 Service Pack 3
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, see the following article in the Microsoft Knowledge Base:
2629969 The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 3 was released
Microsoft SQL Server 2008 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 3 hotfix to an installation of SQL Server 2008 Service Pack 3. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.
Cumulative update package 11 for SQL Server 2008 R2
The fix for this issue was first released in Cumulative Update 11. For more information about how to obtain this cumulative update package for SQL Server 2008 R2, see the following article in the Microsoft Knowledge Base:
2633145 Cumulative update package 11 for SQL Server 2008 R2
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, see the following article in the Microsoft Knowledge Base:
981356 The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 was released
Cumulative update package 4 for SQL Server 2008 R2 SP1
The fix for this issue was first released in Cumulative Update 4. For more information about how to obtain this cumulative update package for SQL Server 2008 R2 SP1, see the following article in the Microsoft Knowledge Base:
2633146 Cumulative update package 4 for SQL Server 2008 R2 SP1
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 SP1 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, see the following article in the Microsoft Knowledge Base:
2567616 The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 SP1 was released
SQL Server 2012
SQL Server 2012 contains the code changes for these fixes. Both trace flag 2562 and 2549 as describe below also apply to SQL Server 2012. However, the fix to reduce contention on the DBCC_MULTIOBJECT_SCANNER latch does not require trace flag 2562 in SQL Server 2012. Those changes were included by default in SQL Server 2012.
More Information
This cumulative update introduces two trace flags to better maximize disk I/O resource usage when a user executes the DBCC CHECKDB command. Although these improvements target only the PHYSICAL_ONLY option, they may also improve the overall performance of the DBCC CHECKDB commands that do not use the PHYSICAL_ONLY option.
Trace flag 2562
This trace flag includes the following changes:
-
Run the DBCC CHECKDB command in a single "batch" regardless of the number of indexes in the database. By default, the DBCC CHECKDB command tries to minimize tempdb resources by limiting the number of indexes or "facts" that it generates by using a "batches" concept. This trace flag forces all processing into one batch.
-
Improve the internal processing for determining which pages to read from the database. This reduces the contention on the DBCC_MULTIOBJECT_SCANNER latch.
One effect of using this trace flag is that the space requirements for tempdb may increase. Tempdb may grow to as much as 5 percent or more of the user database that is being processed by the DBCC CHECKDB command. Therefore, we recommend that you pre-size tempdb to at least 5 percent of your database size when you use this trace flag to avoid automatic growth, which may slow down the performance of the DBCC CHECKDB command.
Note Trace flag 2562 is a session-level trace flag. You can enable it at session level.
Trace flag 2549
The DBCC CHECKDB command builds an internal list of pages to read per unique disk drive across all database files. This logic determines unique disk drives based on the drive letter of the physical file name of each file. If the underlying disks are actually unique when the drive letters or not, the DBCC CHECKDB command would treat these as one disk. When this trace flag is enabled, each database file is assumed to be on a unique disk drive. Do not use this trace flag unless you know that each file is based on a unique physical disk.
Note Trace flag 2549 is a global trace flag. To enable it, you have to use it as startup parameter or use DBCC TRACEON (2549,-1).
Although these trace flags improve the performance of the DBCC CHECKDB commands which target usage of the DBCC CHECKDB option, some users may not see any improvement in performance. While these trace flags improve disk I/O resources usage, the underlying performance of disk resources may limit the overall performance of the DBCC CHECKDB command.
Be aware of the following restrictions:
-
The performance improvements are not designed to improve performance when you use the repair option.
For example, the performance improvements are not designed to improve performance when you use the REPAIR_ALLOW_DATA_LOSS option. -
The improvements do not affect the DBCC CHECKALLOC command.
-
The improvements may help the performance of the DBCC CHECKTABLE command for indexes with many pages. However, the improvements are not targeted to improve the performance of the DBCC CHECKTABLE command.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
