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: , , , , ,
1 comment so far

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!

Failed:(0) Alter failed for server ‘ComputerName\\InstanceName’ February 9, 2009

Posted by Chirag in Database Maintenance Plans, SQL 2005.
Tags: , ,

I had just setup a Maintenance Plan with the first step being “Database Integrity Checks”. I tried to test it and the Job failed. The job step was set to log the output of the step into a file. When I looked at the step execution log file, it had this vague error

Failed: (0) Alter failed for server ‘ComputerName\\InstanceName’

I looked in the SQL Error log and didn’t find anything relating to this.

So, I started a profiler trace on the server and executed the Maintenance Plan job again. I saw that it tried to run the following configuration option

EXEC sys.sp_configure N’user options’, 0


and the job failed with the same error in the step execution log.

Why?… well we all know the strange ways in which some things work with SQL Server sometimes…. I then dug up a KB article on googling which mentioned the same issue… KB945067

This was not of much help as when I checked the settings the “user options” was already set to 0. I was totally confused. Then said lets give the KB suggestion a try. So I manually ran this

sp_configure ‘allow updates’, 0

reconfigure with override

which failed with the following error:

“Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process.”

And voila… it seems I hit the jackpot. Now this was a 64-bit SQL Server Standard Edition Box and one of the other DBA’s had at some point tried to set the “awe enabled” option to 1(don’t ask me why… but someone got a ticking off). The “Lock Pages in Memory” was not set at the OS level causing the “Reconfigure” statement to fail.

Now this caused the Maintenance Plan Job to fail as it tried to unsuccessfully run a Reconfigure statement as it also tried to commit the pending AWE setting.

I reset the “awe enabled” setting to 0 and executed

reconfigure with override

commiting the correct settings. And the day was saved as the Maintenance Plan ran smoothly and I was off to fight another battle.