SQL Server configration settings – MAXDOP, to be or not to be?

As a DBA parallelism or MAXDOP terms do not need any special introduction, but it is highlgy essential to keep up the necessary settings (default value is 0) on your data platform. In many cases MAXDOP helps to achieve optimization for SQL Server processes, since 2008 version release and 64-bit (default) rollout a little overview of query patterns and MAXDOP settings will improve performance at your end.

A chalk out from BOL on MAXDOP:

When SQL Server runs on a computer with more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. To enable the server to determine the maximum degree of parallelism, set this option to 0, the default value. To suppress parallel plan generation, set max degree of parallelism to 1. Set the value to a number greater than 1 to restrict the maximum number of processors used by a single query execution. The maximum value for thedegree of parallelism setting is controlled by the edition of SQL Server, CPU type, and operating system. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored.

Fair enough to understand the importance of MAXDOP and by default there are certain restrictions based on the edition of SQL Server that is being used. For instance SQL Express value limited by 1, both Web & Standard editions does not allow more than 4. Further when you are running Enterprise Edition on a X86 platform then you are limited to 32.

Based on the kind of application data (OLTP or OLAP) you need consider overriding the MAXDOP setting by using query hints. In the case of performing INDEX maintenance related processes you can test to override the max degree of parallelism value during specific key index operations (specify query hint in the statements), REINDEX or REORG. I recommend to read about configuring Parallel Index Operations, limitations and restrictions (for SQL 2012 & SQL 2008 R2) along with Features Supported by the editions of SQL Servr 2012 which is restricted to higher editions of SQL Server (Developer, Enterprise or Evaluation) only.

Not only by using query hints you can also  manage the MAXDOP setting by using trace flag 2528 to control the parallelism for certain DBCC statements such as DBCC CHECKTABLE or CHECKDB or CHECKFILEGROUP, make sure you test this before the production deployment!

In the recent times majority of my projects circles around Design & Architecture of SQL Server platform which comprises configuration standards too. So the minimum available CPU power was 4 * QUAD core or 2 * 16 core with available physical memory of 64GB. Within the data warehousing (OLAP) design by default SQL Server uses majority of available cores as the queries are complex with many joins and dealing with large amounts of data. However for OLTP based scenarios the speed of query results is highly essential, and if your Developers are intelligent-enough then the queries were designed to fetch handful of rows from few tables as opposed to SELECT * FROM <TABLE> or CURSORS usage.

IN order to see whether or not your OLTP queries are affected with parallelism or parallel plan syndrome, you need to spotlight on CXPACKET, EXCHANGE & EXECSYNC wait type by referring to sys.dm_os_wait_stats DMV. Ok now we are pointing out two-different directions and just to expand on what/how this parallelism affects your query results is followed.

For instance a query execution deals with the products that have no matching SALES orders and those SALES orders that are not matched  to a product along with list of Customer address details. In this case the query will have a minimum LEFT JOIN and FULL OUTER JOIN to return the results, also assume that table statistics for SALES (10 million rows), PRODUCT (50K rows) and CUSTOMERS (15K rows) are out dated. In this scenario having a 4 * QUAD core processor server and by default SQL optimizer decides to use all the processors to complete the query execution. But as referred before table statistics were not optimized and in this case only ONE processor will perform the query execution causing remaining processors to show as IDLE (queue waits when worker is idle), which means they are sitting duck. This in turn will cause a HIGH CPU spikes and generate timeout of new connections which is reported as slow down of entire instance. To understand further read about Parallelism tagged blog posts.

Then coming back to default setting of MAXDOP on your servers goes inline with number of available processors & cores. One of the MS KBA refers:

  • For servers that have eight or less processors, use the following configuration where N equals the number of processors: max degree of parallelism = 0 to N .
  • For servers that use more than eight processors, use the following configuration: max degree of parallelism = 8. 
  • For servers that have NUMA configured, max degree of parallelism should not exceed the number of CPUs that are assigned to each NUMA node with the max value capped to 8. This will increase the likelyhood of all parallel threads of a query to be located within a NUMA Node and avoid costly remote node data look ups.
  • For servers that have hyper-threading enabled, the max degree of parallelism value should not exceed the number of physical processors.
  • Use these same guidelines when setting the MAXDOP option for Resource Governor workload groups. 
    • The maximum value of 8 provided in the above guideline is applicable for typical SQL Server activity and the overhead for the exchange operators used in parallel query plans. You could vary this maximum value depending upon your specific application patterns and concurrent activity on the SQL Server. For example:
      • If you have very small number of concurrently executing queries relative to the number of processors, then you can set maxdop to a higher value like 16.
      • If you have very large number of concurrently executing queries relative to the number of processors, then you can set maxdop to a lower value such as 4. 

However the above terminology is classified as proposed solution and I insist to test the referred settings your platform,

Coming back to one of discussions in setting MAXDOP within a SharePoint farm, the MS recommendation is that set MAXDOP to 1- read Best Practices for SQL Server  in a SharePoint farm. But in our case it did not made any difference in query execution results and we had to set the MAXDOP to 4 as the server CPU configuration was 4 * 16 core!

I have written few recipes with regard to SQL Server configuration practices on my book titled: SQL Server 2008 R2 Administration cookbook.