Select the product you need help with

Improvements for the DBCC CHECKDB command may result in faster performance when you use the PHYSICAL_ONLY option

Article ID: 2634571 - View products that this article applies to.

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 with the previous SQL Server 2008 R2, SQL Server 2008 R2 SP1 and SQL Server 2008 fix release.

Back to the top | Give Feedback

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

For example, the DBCC CHECKDB command may not maximize disk I/O usage when consistency checks for a database are performed.

Back to the top | Give Feedback

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

(http://support.microsoft.com/kb/2673382/LN/ )

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. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

2402659

(http://support.microsoft.com/kb/2402659/LN/ )

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, click the following article number to view the article in the Microsoft Knowledge Base:

2673383

(http://support.microsoft.com/kb/2673383/LN/ )

Cumulative update package 4 for SQL Server 2008 Service Pack 3

2629969

(http://support.microsoft.com/kb/2629969/LN/ )

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, click the following article number to view the article in the Microsoft Knowledge Base:

2633145

(http://support.microsoft.com/kb/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, click the following article number to view the article in the Microsoft Knowledge Base:

981356

(http://support.microsoft.com/kb/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, click the following article number to view the article in the Microsoft Knowledge Base:

2633146

(http://support.microsoft.com/kb/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, click the following article number to view the article in the Microsoft Knowledge Base:

2567616

(http://support.microsoft.com/kb/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.

Back to the top | Give Feedback

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 only target 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% 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% 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.

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.

Although these trace flags improve the performance of the DBCC CHECKDB commands which target usage of the PHYSICAL_ONLY 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.

The trace flags can be used at SQL Server startup time or in a session where the DBCC CHECKDB command is run by using the DBCC TRACEON command.

Back to the top | Give Feedback