SQL Server Maintenance Plans and Parallelism – CHECKDB

Many posts and articles that discuss parallelism and SQL Server revolve around query performance and query optimization. Parallelism can affect performance, and some DBAs and database developers spend a great amount of time trying to find the “right” max degree of parallelism (MAXDOP) setting for an instance. Finding that right value is a science and an art, but understanding what degree of parallelism is used by SQL Server for standard maintenance tasks is straight science.

Parallelism and Instance Configuration

To start, I’m working under the assumption that you are familiar with parallelism and understand the implications of leaving max degree of parallelism set to 0, changing it to 1, or setting it to another value. Note: If you’re looking for background reading, I recommend Adam Machanic’s SQL University Parallelism Week posts (Part 1, Part 2, and Part 3), and Paul White’s article, Understanding and Using Parallelism in SQL Server. To understand what impact max degree of parallelism has on maintenance tasks, we need to know its value for an instance, and the easiest way to find it is via sys.configurations:

SELECT [name], [value], [value_in_use]
FROM [sys].[configurations]
WHERE [name] = 'max degree of parallelism';

In my environment, it’s set to 4 for purposes of this demo (I have 8 logical processors on my laptop):

max degree of parallelism configuration

max degree of parallelism configuration

The three maintenance tasks I want to cover in this series are database consistency checks, index rebuilds, and statistic updates.  As you might have guessed from the title, this first post will cover consistency checks.

Database Consistency Checks

In November of last year I blogged about DBCC CHECKDB and parallel checks due to an inconsistency I found in Books Online. The inconsistency has since been fixed in BOL (hooray for Connect items!), and the short story is that any consistency check (CHECKDB, CHECKTABLE, CHECKFILEGROUP, and CHECKCATALOG) is single-threaded in Standard Edition. Regardless of the number of cores in your server and the MAXDOP setting for the instance, any CHECK command will be single-threaded.

In Enterprise Edition, checks can be done in parallel, and the max degree of parallelism is determined by the Query Processor. The Query Processor respects the max degree of parallelism setting for the instance, therefore since I have MAXDOP = 4 for my instance, up to four processors can be used by a CHECK command. There is no MAXDOP option that can be included with a CHECK command; it always respects the value set for the instance.  We can prove this using Extended Events and the sp_statement_completed and query_post_execution_showplan events.

*EDIT: For versions SQL Server 2014 SP2 and higher there is a MAXDOP option you can specify for CHECKDB.  Please see the updated DBCC CHECKDB documentation for complete details.

**Important note here: it is NOT recommended to capture the query_post_execution_showplan event against a live, production system. This event generates significant performance overhead, and you are warned of this when configuring the session via the GUI. If you repeat any of the demos here, please make sure to execute them against a test environment. I really do not want you to bring down your production environment.**

Here are the statements to create the event session, start it, run CHECKDB, then stop the event session. I am using a file target to capture the output, and the path is C:\temp. You may need to modify this path for your environment.

CREATE EVENT SESSION [CapturePlans] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(
ACTION(sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.sp_statement_completed(
ACTION(sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'C:\temp\CapturePlans.xel'),
ADD TARGET package0.ring_buffer(SET max_memory=(102400))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
GO

ALTER EVENT SESSION [CapturePlans]
ON SERVER
STATE=START;
GO

DBCC CHECKDB (AdventureWorks2012) WITH NO_INFOMSGS;
GO

ALTER EVENT SESSION [CapturePlans]
ON SERVER
STATE=STOP;
GO

After stopping the event session, open the .xel file from C:\temp in Management Studio and add the sql_text column to the display to easily find the CHECKDB statement.  In the  screen shot below the query_post_execution_showplan event is highlighted, you can see the sql_text, and I hovered over the showplan_xml to show the first part of the xml version of the plan. Note the red box around QueryPlan DegreeofParallelism…it’s 4, as expected because it’s set to that for the instance.  The query plan is also included in the screen shot to show the full plan.

DBCC CHECKDB with parallelism

DBCC CHECKDB with parallelism

To view the full XML for the plan, just double-click the showplan_xml value and it will open in a new window. Just for fun, if we change MAXDOP for the instance to 1 (which I’ve seen in some OLTP environments) note that CHECKDB now runs single-threaded, regardless of SQL Server version.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

ALTER EVENT SESSION [CapturePlans]
ON SERVER
STATE=START;
GO

DBCC CHECKDB (AdventureWorks2012) WITH NO_INFOMSGS;
GO

ALTER EVENT SESSION [CapturePlans]
ON SERVER
STATE=STOP;
GO

Again, if we open the most recent .xel file in C:\temp and add the sql_text column, we can see that CHECKDB runs single-threaded:

DBCC CHECKDB single-threaded

DBCC CHECKDB single-threaded

Realize that if max degree of parallelism is set to 1 for instance, and you’re running Enterprise Edition, your consistency checks could be taking longer than necessary.  The only way to work around this in Enterprise Edition is to change the max degree of parallelism for the instance, run the check, then change it back.  Alternatively, in some enterprise environments that are 24/7, it may not be desirable to have CHECK commands use a high number of threads.  In that case, lowering max degree of parallelism while CHECKs run may not be an option – you may not want to impact the performance of your regular queries.  Instead, you could disable parallel checking for CHECK commands entirely using trace flag 2528, or you could use Resource Governor to limit CPU use. And one final, interesting note (thanks Paul): CHECKALLOC is always single-threaded, regardless of Edition.

In the next post we’ll look at parallelism and index maintenance.

SQL Server Baselines Series on SQLServerCentral.com

Today the fourth article in my baselines series was published on SQLServerCentral.com.  Listed below are the links to all of the articles, and I’ve decided to continue the series on my blog here.  I truly believe that every SQL Server environment can benefit from having baselines.  I say that all the time, I know, and people seem to agree with me 🙂  But yet there are many systems out there that don’t have baseline data.  The biggest roadblock that I see is deciding what to capture, and then writing the scripts to capture what you want.  The goal of the series I wrote for SQLServerCentral.com was to provide background about why baselines are important, and provide a set of scripts to get started.  The value of baselines may seem obvious to you, but for new DBAs the benefits may not be as apparent, so taking a few minutes to step through them is useful.  The scripts I provide are straight-forward and there’s a reason for that: It’s not rocket science.  Capturing baseline data is really easy to do, and my goal is to provide scripts so DBAs can quickly and easily start capturing data, and hopefully once people realize how simple it was, they will develop their own scripts.

But just because I’ve provided a few scripts doesn’t mean I’m done.  I am going to continue to develop and post scripts here that DBAs can use to set up baselines in their own environment. I recognize that there are some excellent applications available from third party vendors that will capture baseline data, and if your company has the budget to purchase such an application, I would highly recommend doing so. My scripts are not a replacement for any of those applications. But for those of you who don’t have the budget, or perhaps need some data in order to make a case for why you need one of those applications, these scripts will get you started.

If you have any feedback about the scripts, or requests for what I write next, feel free to email me! The scripts are a work in progress and I plan to continually work to make them better.

5 Reasons You Must Start Capturing Baseline Data

Back to Basics: Capturing Baselines on Production SQL Servers

Capturing Baselines on SQL Server: Where’s My Space?

Capturing Baselines on SQL Server: Wait Statistics