DBCC CHECKDB Parallel Checks and SQL Server Edition

It’s been a few weeks since the PASS Summit but I’m still getting the occasional email from people who attended one of my sessions. I consider this a good thing – I believe that if someone follows up with me a few weeks after they attended a session, then something I said stuck with them and it was worth their time to follow up. Some people have had questions about something I said during a session, and others are following up on discussions we had during the week.

I had one question about minimizing the resources used by DBCC CHECKDB, and one of the suggestions I provided was to reduce MAXDOP when CHECKDB was running, to limit the number of processors used by CHECKDB. However, I want to point out that CHECKDB only runs in parallel on Enterprise Edition. You may be aware of this already, as Paul mentions it in one of his posts, and also in the SQL Server 2008 Internals book. But, as I was perusing the DBCC CHECKDB entry online, I discovered that it contains incorrect information. From BOL (emphasis mine):

“Checking Objects in Parallel

By default, DBCC CHECKDB performs parallel checking of objects. The degree of parallelism is automatically determined by the query processor. The maximum degree of parallelism is configured just like parallel queries. To restrict the maximum number of processors available for DBCC checking, use sp_configure. For more information, see Configure the max degree of parallelism Server Configuration Option. Parallel checking can be disabled by using trace flag 2528. For more information, see Trace Flags (Transact-SQL).”

The first sentence is incorrect. DBCC CHECKDB does not perform parallel checking of objects in Standard Edition. At all. CHECKDB is always single-threaded in Standard Edition and I confirmed this with the SQL Server development team.

For Enterprise Edition, CHECKDB respects the maximum degree of parallelism setting configured for the instance. If you have MAXDOP set to 0 for the instance, but want to restrict CHECKDB to only use 2 processors, you need to either change MAXDOP for the instance to 2, or you need to use Resource Governor (also an Enterprise-only feature).

I did enter a Connect item for this, and hopefully the documentation will be fixed soon. I hope this clears up any confusion that may exist.

What Checks Update dbccLastKnownGood?

One of the methods I’ve used to check when a client last ran DBCC CHECKDB on their database is the dbccLastKnownGood value on the boot page (page 9 in the PRIMARY file).  When working with a client a couple weeks ago, the value for dbccclastknowngood was from 2006, but the customer stated they were running integrity checks.  When I investigated their method for checks, I discovered that due to the database size they were running DBCC CHECKALLOC and DBCC CHECKCATALOG weekly, and checking different user tables with DBCC CHECKTABLE at night.  Breaking out the checks between the different commands does not update dbccLastKnownGood – running CHECKDB is the only thing that does.  Let’s have a look…

I have a copy of the AdventureWorks2012 database installed, and I can look at the boot page using DBCC PAGE or DBCC DBINFO.  Both commands are undocumented, but as Paul notes in the links provided, they are safe to run.  I’ll use DBINFO:

DBCC TRACEON (3604);
GO
DBCC DBINFO ('AdventureWorks2012');
GO

The text below is a snippet of the output to show only what’s relevant:

dbi_dbmOldestXactLsn = 0:0:0 (0x00000000:00000000:0000)                 dbi_CloneCpuCount = 0

dbi_CloneMemorySize = 0            dbi_updSysCatalog = 1900-01-01
00:00:00.000

dbi_LogBackupChainOrigin = 0:0:0
(0x00000000:00000000:0000)            

dbi_dbccLastKnownGood = 190001-01 00:00:00.000                         dbi_roleSequence
= 0

dbi_dbmHardenedLsn = 0:0:0 (0x00000000:00000000:0000)                   dbi_localState = 0

dbi_safety = 0                     dbi_modDate = 2012-11-15
09:42:47.813

dbi_verRDB = 184552376             dbi_lazyCommitOption = 0          

dbi_svcBrokerGUID = 10048a46-646c-4c44-8037-8ea6c83f4e2c                dbi_svcBrokerOptions =
0x00000001

If we run DBCC CHECKALLOC, DBCC CHECKCATALOG, and DBCC CHECKTABLE for all system and user tables, you can see that dbccLastKnownGood good does not change (please don’t holler because I’m using a cursor…it does what I need for this example!):

DBCC CHECKALLOC ('AdventureWorks2012') WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO

DBCC CHECKCATALOG ('AdventureWorks2012');
GO

USE [AdventureWorks2012];
GO

DECLARE @schemaname varchar(500);
DECLARE @tablename varchar(500);
DECLARE @fulltable varchar(1000);
DECLARE @string varchar(5000); </span>

DECLARE TableList CURSOR FOR
SELECT [ss].[name],[so].[name]
FROM [sys].[objects] AS so
JOIN [sys].[schemas] AS ss ON [so].[schema_id]=[ss].[schema_id]
WHERE [so].[type] in ('S','U')
ORDER BY [so].[name]; 

BEGIN
OPEN TableList

FETCH NEXT FROM TableList
INTO @schemaname, @tablename;

WHILE @@FETCH_STATUS = 0>
BEGIN
SET @fulltable = @schemaname + '.' + @tablename;
SET @string = N'DBCC CHECKTABLE (''' + @fulltable + ''') WITH ALL_ERRORMSGS, NO_INFOMSGS;'
EXEC (@string)
FETCH NEXT FROM TableList
INTO @schemaname, @tablename;
END 

CLOSE TableList;
DEALLOCATE TableList;
END

<sDBCC DBINFO ('AdventureWorks2012');
GO

Output:

dbi_dbmOldestXactLsn = 0:0:0 (0x00000000:00000000:0000)                 dbi_CloneCpuCount = 0

dbi_CloneMemorySize = 0            dbi_updSysCatalog = 1900-01-01
00:00:00.000

dbi_LogBackupChainOrigin = 0:0:0
(0x00000000:00000000:0000)            

dbi_dbccLastKnownGood = 190001-01 00:00:00.000                         dbi_roleSequence
= 0

dbi_dbmHardenedLsn = 0:0:0 (0x00000000:00000000:0000)                   dbi_localState = 0

dbi_safety = 0                     dbi_modDate = 2012-11-15
09:42:47.813

dbi_verRDB = 184552376             dbi_lazyCommitOption = 0          

dbi_svcBrokerGUID = 10048a46-646c-4c44-8037-8ea6c83f4e2c                dbi_svcBrokerOptions =
0x0000000

No change in dbccLastKnownGood.  What happens if we run DBCC CHECKFILEGROUP?  I created a second filegroup in the AdventureWorks2012 database and created one table in the filegroup, then ran the check on that filegroup:

ALTER DATABASE [AdventureWorks2012]
ADD FILEGROUP [TestFG]; 

ALTER DATABASE [AdventureWorks2012]
ADD FILE ( NAME = N'TestFile', FILENAME = N'D:\Databases\SQL2012\AdventureWorks2012\TestFile.ndf' ,
SIZE = 4096KB , FILEGROWTH = 1024KB )
TO FILEGROUP [TestFG];

CREATE TABLE [dbo].[test] (
[col1] INT,
[col2] INT
)
ON [TestFG]; 

INSERT INTO [dbo].[test] (
col1,
col2
)
VALUES
(1,2),
(3,4); 

DBCC CHECKFILEGROUP (2) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO
DBCC DBINFO ('AdventureWorks2012');
GO

Output:

dbi_dbmOldestXactLsn = 0:0:0 (0x00000000:00000000:0000)                 dbi_CloneCpuCount = 0

dbi_CloneMemorySize = 0            dbi_updSysCatalog = 1900-01-01
00:00:00.000

dbi_LogBackupChainOrigin = 0:0:0
(0x00000000:00000000:0000)            

dbi_dbccLastKnownGood = 2012-11-15
12:25:34.090                        
dbi_roleSequence
= 0

dbi_dbmHardenedLsn = 0:0:0 (0x00000000:00000000:0000)                   dbi_localState = 0

dbi_safety = 0                     dbi_modDate = 2012-11-15
09:42:47.813

dbi_verRDB = 184552376             dbi_lazyCommitOption = 0          

dbi_svcBrokerGUID = 10048a46-646c-4c44-8037-8ea6c83f4e2c                dbi_svcBrokerOptions =
0x0000000

Look at that, it updated.  And finally, we know that CHECKDB will update the value, but what happens if we include the WITH PHYSICAL_ONLY option, where the checks are not quite as involved? (See Paul’s post on check options for VLDBs for additional notes on WITH PHYSICAL_ONLY.)

DBCC CHECKDB ('AdventureWorks2012') WITH PHYSICAL_ONLY;
GO
DBCC DBINFO ('AdventureWorks2012');
GO

Output:

dbi_dbmOldestXactLsn = 0:0:0 (0x00000000:00000000:0000)                 dbi_CloneCpuCount = 0

dbi_CloneMemorySize = 0            dbi_updSysCatalog = 1900-01-01
00:00:00.000

dbi_LogBackupChainOrigin = 0:0:0
(0x00000000:00000000:0000)            

dbi_dbccLastKnownGood = 2012-11-15
12:45:21.173                        
dbi_roleSequence
= 0

dbi_dbmHardenedLsn = 0:0:0 (0x00000000:00000000:0000)                   dbi_localState = 0

dbi_safety = 0                     dbi_modDate = 2012-11-15
09:42:47.813

dbi_verRDB = 184552376             dbi_lazyCommitOption = 0          

dbi_svcBrokerGUID = 10048a46-646c-4c44-8037-8ea6c83f4e2c                dbi_svcBrokerOptions =
0x0000000

The dbccLastKnownGood value does update, as it does with CHECKDB without the PHYSICAL_ONLY.  Therefore, if you’re relying on dbccLastKnownGood to tell you when CHECKDB was last run, realize it may not give you the entire story.

Note: post edited 11/16/2012 to replace screen shots of DBCC DBINFO output with text, as the images did not render well in all browsers.

T-SQL Tuesday #36: What Does Community Mean?

 

TSQL2sDay150x150 I find this month’s T-SQL Tuesday topic quite appropriate, as it follows the 2012 PASS Summit, a time when I get to see so many members of the SQL Server Community. Chris Yates (@YatesSQL) is hosting this month and asks, “What Does Community Mean to You?”  My short answer? #sqlfamily, #sqlhelp and opportunity.

When I think of the SQL Community, it is the people that come to mind – so many people that I have met and become friends with, and so much of it is due to Twitter.  When I think of the SQL Community, I think of Twitter.  I do.  It may seem crazy, and I know that there is more to the community than conversations on Twitter – there is the Summit, there are User Groups, there are SQLSaturdays, there are blogs – the community encompasses so much.  But how do I communicate with everyone?  So frequently, so quickly?  How I have met so many people from all over the US?  All over the world?  Twitter.

Our community is incredibly unique.  None of my friends or family have this kind of cohort – they don’t have the friendships that span the globe.  They don’t regularly converse with colleagues all around the world, working together to answer questions and solve problems.  I’ve lost so many hours of sleep due to chats with Rob Farley (I curse that 17.5 hour time difference!) and Jonathan Kehayias (I’m not convinced that he sleeps) but it’s all worth it.

I have found some really close friends in the Community.  Friends that I would spend time with even if they didn’t work with SQL Server.  Friends that I know I can call or email at any time for anything.  Just over a year ago I blogged about #sqlfamily, and what I wrote then still holds true today.

The SQL Community is also about helping others.  The generosity of individuals continually astounds me.  So many members of the community volunteer for PASS, helping to make the organization so successful.  Many people help others solve technical problems via the #sqlhelp hashtag on Twitter.  And when it’s too much for 140 characters, blog posts are written, questions posted to forums or emails exchanged.  I’ve seen it happen to others, it’s happened to me.  The people of this community share information and provide help, and then celebrate success.

And finally, the SQL Community can provide significant opportunities for people.  The opportunity to meet a favorite blogger at Summit, the opportunity to talk to someone at Microsoft to understand how it really works, the opportunity to meet someone who will become that friend you call when you have life changing news.  And yes, opportunity can be a job opening.  How many jobs have been found through the community?  Too many to count.  I found my team through the SQL Community.  If I were not involved, I do not believe I would have the job I have today.  I love what I do.  I work with five amazing individuals.  People that I respect personally and professionally.  My job, just like the Community, is exhausting, rewarding, and a whole lot of fun.

If you’re not involved, I recommend you think about it.  You never know where it will take you.  And for those of you in this community – I thank you for making it what it is today, and I cannot wait to see where we go in the future.