DBCC CHECKDB – Is the Database Console Command, and is very important to SQL Server. As we know, this command is used to check the physical and logical consistency of a database. When this command is issued it does run few commands that actually do the validation and do check if the pages are consistent and report if they are not. Basically, DBCC CHECKDB does below:
- DBCC CHECKALLOC – This check validates the disk space allocation and compares it with the internal structure of the pages, it can also repair the allocation structure if an anomaly is found. It runs at a database level.
- DBCC CHECKTABLE – This check integrity of the pages and structure for the table or indexed views, it also validates linkages between pages, sort orders, pointers and offset. This check runs at a table and view level.
- DBCC CHECKCATALOG – This check validates the consistency for data type used in the database and compares each of them with the systypes and sysobjects system table. This check runs at a database level.
Last week, one of my friends has called me and discussed what is the process that can help him making DBCC CHECKDB Run Faster? The issue was that the production database for one of his customer is little large in size – 300+ gigs. Earlier, this database was small in size and wasn’t taking a much time to complete DBCC CHECKDB. Earlier it used to complete in 2 hours and now it is taking more than 7 hours to complete and spiking CPU to 100%. After discussing an environment of his client, I found out four things –
- He is running SQL Server 2014 Enterprise edition
- They haven’t configured server’s power plan to use “High Performance”
- The disks are not aligned and configured according to best practice
- However, the database is using file groups and each filegroup on different drive
- The Max Degree of Parallelism is set to default value
Making DBCC CHECKDB Run Faster
With above finding, I have suggested him to change the power option on the server to use “High Performance”. Since the server is already in production and there isn’t any chance that he can reformat the disks according to MS recommendation, he will consider changing them in the later stage. The next and last suggestion is to use either a MAXDOP hint or use a Trace Flag (TF) 2562 or TF 2549.
Using MAXDOP hint with DBCC CHECKDB he can limit SQL Server engine to use the number of CPUs, it would take some more time but then can help cope up the CPU pressure. While using a TF 2562 (a session-level TF) will help him making DBCC CHECKDB Run Faster as it would run the command in a single batch. However, while running DBCC CHECKDB with TF 2562 it may need more space for Tempdb. In case a TF 2549 (a global TF, use it as startup parameter) is used, it will consider each database file is on the unique disk drive. This Trace Flags – 2549 or 2562 help improves the I/O resources and thus helping the overall performance of the DBCC CHECKDB.
It may interest you to know what trace flag is which I have described here. You may also want to read on various troubleshooting tips by browsing troubleshooting.
Do let me know if you have used some other tip making DBCC CHECKDB Run Faster and I will be more than happy to add them and modify my article.
