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.