A faster CHECKDB – Part I

Earlier this year I travelled to Japan having the opportunity to visit some of our customers and prospective customers. One feedback I received loud and clear was poor performance when using DBCC CHECKDB as customers have moved into the TB range of databases on a regular basis. I had certainly heard this feedback before from other CSS engineers but not the extent of complaints I heard while in Japan. I had never really investigated these claims of performance issues because I suppose I fell under the same feelings I had and seen for years of “CHECKDB takes as long as it takes”.

On that trip I met Cameron Gardiner from the SQL Customer Advisory Team (SQL CAT). Cameron is an expert with SAP systems and focuses his efforts at Microsoft on SAP running on SQL Server. Cameron spent tine to explain what a problem the performance of CHECKDB had become for his largest SAP accounts using SQL Server.

So I went back to the US determined to investigate the cause and possible solutions for this. The result of this work is now available in the latest cumulative update for SQL Server 2008 R2 customers. You can read the basics of how to apply these two trace flags with this update in this article. A SQL Server 2008 version of these changes will also be available early next year.

I’m on vacation for the rest of the year, but when I come back, I’ll create another blog post (Part II) with the details behind these changes, how we did this, and how this enhancement might help you when using DBCC CHECKDB.

Bob Ward
Microsoft