Notes on SQL

Background

While looking at a system that I’m unfamiliar with, I queried the server setting for Maximum Degree of Parallelism (MAXDOP). It hadn’t been set to what I believed it should have been but I was then shown the MAXDOP setting against the database properties. I wasn’t aware that this existed, and it raised a few questions.

Overview

With SQL 2016, the facility to set MAXDOP against the database was introduced. So, with the ability to set MAXDOP against the SQL Server and then set it against the individual databases, how does that work out? Documentation for this appears to be rather sparse – plenty of articles tell you that it is a new feature, but none tell you the finer details. Can the MAXDOP for the database be higher than the MAXDOP you’ve set the server-level? If MAXDOP of 0 for the server means you can use all the processors, why does the database creation default the database-level MAXDOP to 0? Trying to blank out that value for the database gives the very meaningful ‘Property value is not valid’ message. And the so-called help-text just states ‘Max DOP’. I’m old enough to remember when Microsoft’s SQL Server documentation was quite good.

So, I needed to work this out.

Test Environment.

I have SQL Server 2022 on my laptop and the AdventureWorks2022 database installed.

My plan was to tinker with the MAXDOP settings and, by examining the query plan of a test query, map out when parallelism was used and which setting it referred to. Within the execution plan there is a property ‘EstimatedAvailableDegreeOfParallelism’. This is the number of processors that the query can use if it decides to use parallelism, although it may well use less.

To give the best possible chance of a query going parallel, I have set the server’s ‘Cost Threshold for Parallelism’ to 0.

Figure 1: Setting Cost Threshold to zero

For the testing, I’ll be changing the values within the server’s ‘Max Degree of Parallelism’ (see previous image, below ‘Cost Threshold for Parallelism’) and the database’s MAXDOP setting.

Figure 2: The Database MAXDOP Property

The query is nothing exciting, just designed to create a reasonable load upon the server’s resources and encourage it to use parallelism.

Listing 1: The Query against the AdventureWorks2022 database

SELECT DISTINCT
    p.Name AS ProductName,
    SUM(sod.OrderQty) AS TotalOrderQuantity,
    SUM(sod.LineTotal) AS TotalSalesAmount,
    COUNT(DISTINCT soh.SalesOrderID) AS NumberOfOrders,
	pp.FirstName AS Firstname,
	pp.LastName AS Surname
FROM 
    Sales.SalesOrderDetail sod
JOIN 
    Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
JOIN 
    Production.Product p ON sod.ProductID = p.ProductID
JOIN 
    Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
JOIN 
    Person.Person pp ON soh.CustomerID = pp.BusinessEntityID
GROUP BY 
    p.Name, pp.FirstName, pp.LastName
ORDER BY 
    TotalSalesAmount DESC;

Including the actual query plan with the executions of this code, will show if the query performed in parallel and what the maximum parallelism value was set to (in ‘EstimatedAvailableDegreeOfParallelism’).

Figure 3: The Parallellism Icon and EstimatedAvailableDegreeOfParallelism

Test Execution

I now change the values for the MAXDOP on the server and the database. Each time a value is changed I check the execution plan and see if it has used parallelism or not and the value for the EstimatedAvailableDegreeOfParallelism.

The results of this are listed below and show that MAXDOP of 0 for the database setting uses whatever the MAXDOP setting is for the server.

Figure 4: Test Results

Conclusion

If the database-level MAXDOP is 0, execution of the queries obeys the server-level MAXDOP setting.

If the database-level MAXDOP is not 0, then this setting is used by the queries for that database.

So, whereas the server-level MAXDOP effectively means ‘use all of the processors available’, when it set to 0 on the database settings it basically means ‘do whatever the server settings dictate’.