Faster DBCC CHECKDB Released in SQL 2008 R2 SP1 CU4 : Traceflag 2562 & 2549
CHECKDB Times are Reduced with New CHECKDB Functionality
Customers with larger databases are recommended to upgrade to SQL 2008 R2 SP1 Cumulative Update 4 to benefit from new improved CHECKDB functionality described in KB2634571.
CHECKDB Runtimes are Currently Too Long
SAP on Windows SQL customers now have databases 16-17TB. Customers larger than 10TB are pretty common. At the same time as mandating PAGE compression as compulsory for all customers we have also responded to requests from customers to speed up the CHECKDB process.
Please note that there are two mechanisms for detecting DB corruption in SQL Server. DBCC CHECKDB is the first. The second is the PAGE_VERIFY = CHECKSUM database option. Each time a page is read or written to the checksum is verified. This is a highly effective mechanism and should always be set on all SAP on SQL systems using SQL 2005 or higher.
How to Activate DBCC CHECKDB Page & Object Batching
KB2634571 details traceflag 2562 & 2549. SAP on SQL Server 2008 and higher customers should usually run traceflag 1117. The procedure for how to set permanent traceflags at start up is documented in Note 1238993 - Proportional File Auto-Growth with SQL Server 2008.
An alternative to setting this traceflag is to run these commands:
DBCC TRACEON (2562);
DBCC TRACEON (2549);
DBCC TRACESTATUS;
These commands will set and display the traceflags, but only for the specific SQL Server session in use by the user that executed the commands. The traceflags will not be set for other users and will be lost when the user disconnects or SQL Server is restarted.
Several SAP customers tested this new functionality with a “private build” of SQL Server and achieved very good results. Depending on SAN configuration and several other factors, the runtime of CHECKDB was cut in half.
The biggest benefits came when running CHECKDB with Physical Only.
DBCC CHECKDB WITH PHYSICAL_ONLY;
If you detect corruption on a SQL Server database please immediately open a PSS Case or SAP OSS message. Based on previous experience causes can be : incorrect dependencies on mountpoints, HBA device driver bugs, SAN issues, faulting H/W etc.
Increased IO & Tempdb Consumption
Activating these traceflags can lead to increased IO due to the higher throughput of the CHECKDB process. It is also possible that Tempdb will grow considerably potentially reaching a maximum of around 10% of the database size. It is critical that there are multiple tempdb datafiles. The table below shows a typical configuration for a medium sized SAP system. Tempdb datafiles are “co-located” onto the same disks as the SAP database. For ECC and other OLTP type systems 1 tempdb per disk is adequate in most cases. BW systems should have the same number of tempdb datafiles as the <SID> database. More information can be found in this blog. A total tempdb database size of around 10GB is a good starting point when utilizing this new CHECKDB functionality. In the example below sizing the tempdb datafiles and log files at 2GB each is adequate for a SAP system with a 2TB to 6TB size database. Ensure that there is plenty of room on the filesystem for the tempdb datafiles and log file to grow
CHECKDB vs. CHECKDB WITH PHYSICAL_ONLY
In most scenarios a CHECKDB WITH PHYSICAL_ONLY should be sufficient, however it would still be advisable to periodically run a full CHECKDB. The differences between a CHECKDB and a CHECKDB WITH PHYSICAL_ONLY are explained here.
Limits the checking to the integrity of the physical structure of the page and record headers and the allocation consistency of the database. This check is designed to provide a small overhead check of the physical consistency of the database, but it can also detect torn pages, checksum failures, and common hardware failures that can compromise a user's data.
A full run of DBCC CHECKDB may take considerably longer to complete than earlier versions. This behavior occurs because:
· The logical checks are more comprehensive.
· Some of the underlying structures to be checked are more complex.
· Many new checks have been introduced to include the new features.
Therefore, using the PHYSICAL_ONLY option may cause a much shorter run-time for DBCC CHECKDB on large databases and is recommended for frequent use on production systems. We still recommend that a full run of DBCC CHECKDB be performed periodically. The frequency of these runs depends on factors specific to individual businesses and production environments.
Feedback from Customers : Please post your comments in this Blog
Depending on the level of feedback and comments from SAP on SQL Server customers, SAP might consider adding some supporting functionality to DBA Cockpit. Please feel free to post your comments and feedback in this blog. Useful feedback would be DB size, runtime before using this functionality and runtime after using this functionality.
More detailed information about this functionality will be posted to PSS Engineers Blog
Thanks
Special thanks to Bob Ward.
Recommended Blog: https://blogs.msdn.com/b/psssql/
Comments
Anonymous
March 06, 2012
I want to test out this on SQL Server 2012, can i just enable flag 2562 and 2549? Please advise. thx BillAnonymous
April 15, 2012
Yes, sure - just enable the trace flag either at startup or globally and run. Ensure Tempdb is sized appropriately