jump to navigation

Multithreaded DBCC Checkdb when MAXDOP = 1 March 4, 2010

Posted by Chirag in Database Maintenance Plans, SQL 2005, SQL 2008, SQL Server 2008, T-SQL.
Tags: , , , , ,
trackback

I know I am posting after a long time, so have just decided to wipe of the dust off my blogging keyboard, bring out my rusty creative writing skills and start posting again.

So here goes again… with a post to request your support and vote on a connect item if you feel my pain.

We have a customer who has several servers with many VLDB’s hosted on each of them. We run a nightly maintenance plan for general housekeeping and this tends to take a very long time to finish. An average run of CHECKDB with the PHYSICAL_ONLY option on weekdays takes over 4 hours and full CHECKDB on Sunday’s can take up to 6-8 hours to finish. This causes the job to overshoot the nightly maintenance window and in turn translates to performance problems for query response times as it is a 24×7 shop.

Besides all the necessary checks that CHECKDB does to check the integrity of the database it can run single threaded or multithreaded if more  processors are available. For reasons of query response and runtime predictability requirements the client has MAXDOP = 1 configured at the SQL Server instance level. This causes CHECKDB to run single threaded and contributes to extending the overall runtime of CHECKDB.

If like me you feel that there should be a setting to allow us to force CHECKDB to run multi threaded even if MAXDOP = 1 then please vote on this connect item: https://connect.microsoft.com/SQLServer/feedback/details/538754/introduce-setting-to-force-dbcc-checkdb-to-run-multi-threaded-when-maxdop-1

I have requested this to be implemented as:

- Trace Flag to allow global setting

- Switch on the DBCC CHECKDB command

Many thanks in advance if you vote and hopefully Microsoft introduce this soon!

About these ads

Comments»

1. Richard Douglas - July 23, 2010

This is an excellent suggestion to which I have added my support by voting


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: