You are currently offline, waiting for your internet to reconnect

Your browser is out-of-date

You need to update your browser to use the site.

Update to the latest version of Internet Explorer

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

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.

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.

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. 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 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 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, click the following article number to view the 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, click the following article number to view the 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, click the following article number to view the 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, click the following article number to view the 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, click the following article number to view the 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 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.

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 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.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Properties

Article ID: 2634571 - Last Review: 12/19/2014 23:17:00 - Revision: 5.0

  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2008 R2 Express with Advanced Services
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Standard Edition for Small Business
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2008 R2 Workgroup
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Service Pack 3
  • Microsoft SQL Server 2008 Standard Edition for Small Business
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2008 Service Pack 2
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Standard
  • SQL Server 2012 Enterprise Core
  • Microsoft SQL Server 2012 Express
  • kbqfe kbfix kbexpertiseadvanced kbsurveynew KB2634571
Feedback
&did=1&t=">&did=1&t=">trl = ""; document.write(" meta');m.name='ms.dqp0';m.content='true';document.getElementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?"> 050&did=1&t=">