In this article, I’m going to show how that can be converted to an Advisory Condition for the SentryOne Monitoring Platform. I’m also going to show how an action can be created to set MAXDOP based on the recommended value.
Why an Advisory Condition?
The script from the original article is useful if you need to evaluate a SQL Server quickly, or if it’s one that you don’t regularly manage. However, if the SQL Server is part of your normally managed set, you want to be proactively notified if there is something misconfigured. Advisory Conditions are great for doing that.
The included set of Advisory Conditions includes one that will alert you if the configured MAXDOP setting is modified. This is very useful, but it won’t warn you if the MAXDOP setting no longer meets the Microsoft recommendation. There are several reasons this could occur. In some cases the original configured value for MAXDOP wasn’t set appropriately. In other cases, you may have additional hardware installed into your server, or in the case of virtual machines, have additional processors allocated to the VM.
The Advisory Condition also can be tied to an Action. This provides an easy way to correct the issue that was identified by the Advisory Condition. In this case, the Action will be set to use the maximum recommended value for MAXDOP.
As noted in the previous article, you should not blindly apply the recommendation—consider your SQL Server’s workload and resources.
Adjusting the Script
Advisory Conditions expect a boolean result—is the condition true or false? The original script returns a message using ‘RAISERROR’—that’s not what we want for an Advisory Condition. Instead, we want to return a value that we can evaluate easily. In this case, we want to feed the value into an action as well, so we want the recommended value for MAXDOP to be returned. This requires a few modifications to the script.
In the original version, a ‘ResultMessage’ variable is set for each condition. In the updated version, a ‘RecommendedMaxDOP’ variable is set as well. This variable will hold the maximum recommended value for MAXDOP, based on the SQL Server configuration. In each portion of the script that checks the configuration, you will see code similar to this:
IF @LogicalProcessorPerNumaNodeCount > @LogicalProcessorThreshold
AND @EffectiveMaxDOP > @LogicalProcessorThreshold
BEGIN
SET @ResultMessage = N'MAXDOP should not exceed a value of 16.';
SET @RecommendedMaxDOP = 16;
END
There is special handling for the scenario where the current MAXDOP value is 1. A setting of 1 disables parallelism for the server, and based on the Microsoft guidelines, a value of 1 is always in the recommended range. However, for most workloads, disabling parallelism at the server level is not the best choice. It can be set at the workload level using Resource Governor, at the database level, or at the query level. Using one of these options for finer control is a better approach than globally disabling parallelism. Because of this, the script will return the recommended maximum for MAXDOP if the current setting is 1.
You can exclude this section from the script if you have intentionally disabled parallelism for the server. This can be required in some cases. For example, some third party applications don’t work well with parallelism. If it is the only application on the server, disabling it server wide may be an appropriate choice.
IF @EffectiveMaxDOP = 1
BEGIN
SET @RecommendedMaxDOP = CASE WHEN @LogicalProcessorPerNumaNodeCount <= @LogicalProcessorThreshold
THEN @LogicalProcessorPerNumaNodeCount
WHEN @LogicalProcessorPerNumaNodeCount > @LogicalProcessorThreshold
AND (@LogicalProcessorPerNumaNodeCount / 2) <= @LogicalProcessorThreshold
THEN (@LogicalProcessorPerNumaNodeCount / 2)
WHEN @LogicalProcessorPerNumaNodeCount > @LogicalProcessorThreshold
THEN @LogicalProcessorThreshold
END;
RAISERROR('MAXDOP is set to 1, which suppresses parallel plan generation.', 0, 1);
END
At the end of the script, the ‘ResultMessage’ is still checked, and an informational message is raised. The change from the original script is that the message is always raised as informational, regardless of whether the current MAXDOP value is in the recommended range or not. Finally, the ‘RecommendedMaxDOP’ value is returned from the script. This provides a value that is easily evaluated in the Advisory Condition—if it is greater than 0, a change is recommended.
IF @ResultMessage IS NULL
BEGIN
RAISERROR('MAXDOP setting is in the recommended range. Reported values - NUMA: [%d], Logical Processor: [%d], MAXDOP: [%d]',
0, 1, @NumaNodeCount, @LogicalProcessorPerNumaNodeCount, @EffectiveMaxDOP);
END
ELSE
BEGIN
RAISERROR('MAXDOP is not set based on recommendations in KB2806535. %s Reported values - NUMA: [%d], Logical Processor: [%d], MAXDOP: [%d]',
0, 1, @ResultMessage, @NumaNodeCount, @LogicalProcessorPerNumaNodeCount, @EffectiveMaxDOP);
END
SELECT @RecommendedMaxDOP;
Adding the Advisory Condition
The steps in adding an Advisory Condition are covered well in the help documentation, so this will be a quick overview.
If you want to save some time, you can just import the condition file. But setting up an Advisory Condition isn’t difficult and if you haven’t been through it before, I would recommend giving it a try.
When creating the Advisory Condition, choose the SQL Server type.
For the properties, use the following list:
*You can set this to run more frequently, but it’s not necessary in most environments.
When these settings are made, your Advisory Condition should look like this one:
The next step is to add a ‘numeric’ condition using a SQL Server Query. Set it to run in the ‘master’ database. Add the modified script to the query box, and set the comparison value to be ‘Is Greater Than Explicit Value’ of 0. This will cause the Advisory Condition to fire when the recommendation is not met.
Now save the Advisory Condition, and you are all set.
Monitoring the Advisory Condition
By default, the Advisory Condition will send any failures to the default Alerting channels, so there’s no need to do additional configuration if you just want to be alerted in the same way that you are for other SentryOne alerts. Of course, you can add other actions if you desire.
Please Note: Implementing the following will mean that your SQL Servers will have their MAXDOP setting updated automatically. Please consider whether this fits your requirements before implementing this.
If you want the Advisory Condition to automatically configure MAXDOP to the recommended setting, you can tie it to an Execute SQL action, with these contents:
DECLARE @firstMatch NVARCHAR(50) = N'SQL Server Query [';
DECLARE @message NVARCHAR(MAX) = '<%Message%>';
DECLARE @firstPosition INT = CHARINDEX(@firstMatch, @message) + LEN(@firstMatch);
DECLARE @lastPosition INT = CHARINDEX(N']', @message, @firstPosition);
IF @firstPosition = 0 OR @lastPosition = 0
BEGIN
RAISERROR('Could not find expected delimiters in text: %s', 11,1, @message);
END
DECLARE @recommendation INT = TRY_CAST(SUBSTRING(@message, @firstPosition, @lastPosition - @firstPosition) as INT);
IF @recommendation IS NOT NULL
BEGIN
EXEC sp_configure 'max degree of parallelism', @recommendation;
RECONFIGURE WITH OVERRIDE;
END
This parses the recommended value out of the `<%Message%>` value passed to the action, and applies it with `sp_configure`.
Note that you may need to run this script to allow access to advanced configuration options before this will work.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
Using this script as an Advisory Condition is particularly useful if you manage a large SQL Server estate, or you have a dynamic environment where VMs are moving and may have resources reallocated. I hope you find this useful, and I’d love to hear any feedback on improvements.
John Welch is the Chief Technology Officer at SentryOne, where he leads a team in the development of a suite of data and BI products that make monitoring, building, testing, and documenting data solutions faster and more efficient. John has been working with data, business intelligence, and data warehousing technologies since 2001. He was awarded as a Microsoft Most Valued Professional (MVP) 2009 – 2016 due to his commitment to sharing his knowledge with the IT community, and is an SSAS Maestro. John is an experienced speaker, having given presentations at Professional Association for SQL Server (PASS) conferences, the Microsoft Business Intelligence conference, Software Development West (SD West), Software Management Conference (ASM/SM), SQL Bits, and others. He has also contributed to multiple books on SQL Server and business intelligence.
