To start, I\u2019m 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\u2019re looking for background reading, I recommend Adam Machanic\u2019s SQL University Parallelism Week posts (Part 1<\/a>, Part 2<\/a>, and Part 3<\/a>), and Paul White\u2019s article, Understanding and Using Parallelism in SQL Server<\/a>.<\/em> 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:<\/p>\n In my environment, it\u2019s set to 4 for purposes of this demo (I have 8 logical processors on my laptop):<\/p>\n The three maintenance tasks I want to cover in this series are database consistency checks, index rebuilds, and statistic updates.\u00a0 As you might have guessed from the title, this first post will cover consistency checks.<\/p>\n Database Consistency Checks<\/strong><\/p>\n In November of last year I blogged about DBCC CHECKDB and parallel checks<\/a> 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.<\/p>\n 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.\u00a0 We can prove this using Extended Events and the sp_statement_completed and query_post_execution_showplan events.<\/p>\n *EDIT: For versions SQL Server 2014 SP2 and higher there is a MAXDOP option you can specify for CHECKDB.\u00a0 Please see the updated DBCC CHECKDB<\/a> documentation for complete details.<\/em><\/p>\n **Important note here: it is NOT<\/em> <\/strong>recommended to capture the query_post_execution_showplan event against a live, production system. This event generates significant<\/em> <\/strong>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.**<\/span><\/p>\n 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.<\/p>\n 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.\u00a0 In the\u00a0 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.\u00a0 The query plan is also included in the screen shot to show the full plan.<\/p>\n 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.<\/p>\n Again, if we open the most recent .xel file in C:\\temp and add the\u00a0sql_text column, we can see that CHECKDB runs single-threaded:<\/p>\n\r\nSELECT [name], [value], [value_in_use]\r\nFROM [sys].[configurations]\r\nWHERE [name] = 'max degree of parallelism';\r\n<\/pre>\n
![\"max](\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2013\/02\/maxdop.jpg\")
<\/a>\r\nCREATE EVENT SESSION [CapturePlans] ON SERVER\r\nADD EVENT sqlserver.query_post_execution_showplan(\r\nACTION(sqlserver.plan_handle,sqlserver.sql_text)),\r\nADD EVENT sqlserver.sp_statement_completed(\r\nACTION(sqlserver.sql_text))\r\nADD TARGET package0.event_file(SET filename=N'C:\\temp\\CapturePlans.xel'),\r\nADD TARGET package0.ring_buffer(SET max_memory=(102400))\r\nWITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,\r\nMAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);\r\nGO\r\n\r\nALTER EVENT SESSION [CapturePlans]\r\nON SERVER\r\nSTATE=START;\r\nGO\r\n\r\nDBCC CHECKDB (AdventureWorks2012) WITH NO_INFOMSGS;\r\nGO\r\n\r\nALTER EVENT SESSION [CapturePlans]\r\nON SERVER\r\nSTATE=STOP;\r\nGO\r\n<\/pre>\n
![\"DBCC](\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2013\/02\/checkdb_isparallel-1024x420.png\")
<\/a>\r\nsp_configure 'show advanced options', 1;\r\nGO\r\nRECONFIGURE WITH OVERRIDE;\r\nGO\r\nsp_configure 'max degree of parallelism', 1;\r\nGO\r\nRECONFIGURE WITH OVERRIDE;\r\nGO\r\n\r\nALTER EVENT SESSION [CapturePlans]\r\nON SERVER\r\nSTATE=START;\r\nGO\r\n\r\nDBCC CHECKDB (AdventureWorks2012) WITH NO_INFOMSGS;\r\nGO\r\n\r\nALTER EVENT SESSION [CapturePlans]\r\nON SERVER\r\nSTATE=STOP;\r\nGO\r\n<\/pre>\n
![\"DBCC](\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2013\/02\/checkdb_noparallel-1024x420.png\")
<\/a>