{"ID":"801f0803-c48d-413f-9af0-425dc3cc1200","VersionNumber":13,"Name":"Server MAXDOP in Recommended Range","Description":"\r\n\r\n\t\r\n\t\t\r\n\t\t\r\n\t\t\r\n\t\r\n\t\r\n\t\t

Checks that the configured value for MAXDOP is in the recommended range, as described in this KB article: https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi

\r\n\r\n","AppliesToObjectTypeID":"0a11a887-823a-4461-87af-321cad1c3623","ConditionCategoryID":"805a84c9-7f1b-4b4d-a9ad-3dddcf9e0f17","OwnerObjectID":"b7c74f24-1a45-4115-8262-d7613878bbd6","OwnerObjectTypeID":"894de672-3fc0-4779-9a0d-880d4c207c77","RuleDefinition":"{\"OperationTypeID\":\"6c3d38f1-9317-4258-972a-cfee0a0d76b5\",\"Children\":[{\"OperationTypeID\":\"8fa3cd6a-960c-43bb-96e0-94092fc2c296\",\"ValueDataTypeID\":\"b35a57f5-b8f6-4e4e-9380-e9d328e3eabf\",\"Left\":{\"ValueTypeID\":\"aa61ee90-8a95-4fdf-a208-b9a4afa26d9d\",\"Database\":\"master\",\"Query\":\"DECLARE @ProductVersion NVARCHAR(50);\\r\\nDECLARE @Major INT;\\r\\nDECLARE @NumaNodeCount INT;\\r\\nDECLARE @LogicalProcessorPerNumaNodeCount INT;\\r\\nDECLARE @EffectiveMaxDOP INT;\\r\\nDECLARE @LogicalProcessorThreshold INT;\\r\\nDECLARE @ResultMessage NVARCHAR(200);\\r\\nDECLARE @RecommendedMaxDOP INT = 0;\\r\\n\\r\\nSET @ProductVersion = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(50));\\r\\nSET @Major = CAST(LEFT(@ProductVersion, CHARINDEX('.', @ProductVersion)-1) AS INT);\\r\\n\\r\\nIF @Major < 13\\r\\nBEGIN\\r\\n\\tRAISERROR('This script is intended for SQL Server 2016 or higher. It will not work on version [%s].', 11, 1, @ProductVersion);\\r\\nEND\\r\\n\\r\\n-- Get the MAXDOP setting\\r\\nSELECT \\r\\n @EffectiveMaxDOP = CAST(value_in_use AS INT)\\r\\nFROM \\r\\n sys.configurations\\r\\nWHERE \\r\\n [name] = N'max degree of parallelism';\\r\\n\\r\\nIF @EffectiveMaxDOP = 0\\r\\nBEGIN\\r\\n\\tSELECT \\r\\n\\t\\t@EffectiveMaxDOP = COUNT(*)\\r\\n\\tFROM\\r\\n\\t\\tsys.dm_os_schedulers\\r\\n\\tWHERE \\r\\n\\t\\tscheduler_id <= 1048575\\r\\n\\t\\tAND is_online = 1;\\r\\nEND\\r\\n\\r\\n-- Get the NUMA node count\\r\\n-- Get the logical processors per numa node\\r\\nSELECT \\r\\n @NumaNodeCount = COUNT(*), @LogicalProcessorPerNumaNodeCount = MAX(online_scheduler_count)\\r\\nFROM \\r\\n sys.dm_os_nodes \\r\\nWHERE node_id <> 64; --Excluded DAC node\\r\\n\\r\\n--##TestPlaceholder##--\\r\\n\\r\\nIF @NumaNodeCount < 1 OR @LogicalProcessorPerNumaNodeCount < 1\\r\\nBEGIN\\r\\n RAISERROR('Could not capture NUMA node or logical processor count. Reported values - NUMA: [%d], Logical Processor: [%d]', \\r\\n 11,1, @NumaNodeCount, @LogicalProcessorPerNumaNodeCount);\\r\\nEND\\r\\n\\r\\nSET @LogicalProcessorThreshold = IIF(@NumaNodeCount = 1, 8, 16);\\r\\n\\r\\n--If NUMA = 1 and LogiProcs <= 8 THEN ASSERT(MaxDop <= LogiProcs)\\r\\n--If NUMA > 1 and LogiProcs <= 16 THEN ASSERT(MaxDop <= LogiProcs)\\r\\nIF @LogicalProcessorPerNumaNodeCount <= @LogicalProcessorThreshold\\r\\n AND @EffectiveMaxDOP > @LogicalProcessorPerNumaNodeCount\\r\\nBEGIN \\r\\n\\tSET @ResultMessage = N'MAXDOP should be less than or equal to the Logical Processor count per NUMA node.';\\r\\n\\tSET @RecommendedMaxDOP = @LogicalProcessorPerNumaNodeCount;\\r\\nEND\\r\\nELSE\\r\\nBEGIN\\r\\n\\t-- If NUMA = 1 and LogiProcs > 8 THEN ASSERT(MaxDop == 8)\\r\\n\\tIF @NumaNodeCount = 1 \\r\\n\\t\\tAND @LogicalProcessorPerNumaNodeCount > @LogicalProcessorThreshold\\r\\n\\t\\tAND @EffectiveMaxDOP <> @LogicalProcessorThreshold\\r\\n\\tBEGIN \\r\\n\\t\\tSET @ResultMessage = N'MAXDOP should be equal to 8.';\\r\\n\\t\\tSET @RecommendedMaxDOP = 8;\\r\\n\\tEND\\r\\n\\t-- If NUMA > 1 and LogiProcs > 16 THEN ASSERT(MaxDop <= 16 & MaxDop <= (LogiProcs / 2))\\r\\n\\tELSE\\r\\n\\tBEGIN\\r\\n\\t\\tIF @LogicalProcessorPerNumaNodeCount > @LogicalProcessorThreshold \\r\\n\\t\\t\\t\\tAND @EffectiveMaxDOP > @LogicalProcessorThreshold\\r\\n\\t\\tBEGIN \\r\\n\\t\\t\\tSET @ResultMessage = N'MAXDOP should not exceed a value of 16.';\\r\\n\\t\\t\\tSET @RecommendedMaxDOP = 16;\\r\\n\\t\\tEND\\r\\n\\t\\tELSE IF @LogicalProcessorPerNumaNodeCount > @LogicalProcessorThreshold \\r\\n\\t\\t\\t\\tAND (@LogicalProcessorPerNumaNodeCount / 2) <= @LogicalProcessorThreshold\\r\\n\\t\\t\\t\\tAND @EffectiveMaxDOP <> (@LogicalProcessorPerNumaNodeCount / 2)\\r\\n\\t\\tBEGIN \\r\\n\\t\\t\\tSET @ResultMessage = N'MAXDOP should be set at half the number of logical processors per NUMA node with a MAX value of 16.';\\r\\n\\t\\t\\tSET @RecommendedMaxDOP = (@LogicalProcessorPerNumaNodeCount / 2);\\r\\n\\t\\tEND\\r\\n\\tEND\\t\\r\\nEND\\r\\nIF @EffectiveMaxDOP = 1\\r\\nBEGIN\\r\\n\\tSET @RecommendedMaxDOP = CASE WHEN @LogicalProcessorPerNumaNodeCount <= @LogicalProcessorThreshold\\r\\n\\t\\t\\t\\t\\t\\t\\t\\t THEN @LogicalProcessorPerNumaNodeCount\\r\\n\\t\\t\\t\\t\\t\\t\\t\\t WHEN @LogicalProcessorPerNumaNodeCount > @LogicalProcessorThreshold \\r\\n\\t\\t\\t\\t\\t\\t\\t\\t AND (@LogicalProcessorPerNumaNodeCount / 2) <= @LogicalProcessorThreshold\\r\\n\\t\\t\\t\\t\\t THEN (@LogicalProcessorPerNumaNodeCount / 2)\\r\\n\\t\\t\\t\\t\\t\\t\\t\\t WHEN @LogicalProcessorPerNumaNodeCount > @LogicalProcessorThreshold\\r\\n\\t\\t\\t\\t\\t\\t\\t\\t THEN @LogicalProcessorThreshold\\r\\n\\t\\t\\t\\t\\t\\t\\t END;\\r\\n\\r\\n\\tRAISERROR('MAXDOP is set to 1, which suppresses parallel plan generation.', 0, 1);\\r\\nEND\\r\\n\\r\\nIF @ResultMessage IS NULL\\r\\nBEGIN\\r\\n\\tRAISERROR('MAXDOP setting is in the recommended range. Recommended value: [%d]. Reported values - NUMA: [%d], Logical Processor: [%d], MAXDOP: [%d]', \\r\\n\\t\\t\\t 0, 1, @RecommendedMaxDOP, @NumaNodeCount, @LogicalProcessorPerNumaNodeCount, @EffectiveMaxDOP);\\r\\nEND\\r\\nELSE\\r\\nBEGIN\\r\\n\\tRAISERROR('MAXDOP is not set based on recommendations in KB2806535. %s Recommended value: [%d]. Reported values - NUMA: [%d], Logical Processor: [%d], MAXDOP: [%d]', \\r\\n\\t\\t\\t 0, 1, @ResultMessage, @RecommendedMaxDOP, @NumaNodeCount, @LogicalProcessorPerNumaNodeCount, @EffectiveMaxDOP);\\r\\nEND\\r\\nSELECT @RecommendedMaxDOP;\",\"InstanceType\":0},\"ComparisonType\":2,\"Right\":{\"ValueTypeID\":\"07f87b7f-c063-47a2-a5be-772ba85ed827\",\"Value\":\"0\"},\"ID\":\"1\"}],\"BooleanOperationType\":0,\"ID\":\"0\"}","EvaluationFrequency":"1.00:00:00","IdlePeriod":"00:00:30","MaximumAllowedDuration":"00:00:05","AntiConditionID":"cb90f340-af83-4f7b-afd2-cc2b1a9b386a","MinWindowsVersion":null,"MaxWindowsVersion":null,"MinSQLServerVersion":"13.0","MaxSQLServerVersion":null,"MinSSASVersion":null,"MaxSSASVersion":null,"MinVmwareVersion":null,"MaxVmwareVersion":null,"MinSqlDbVersion":null,"MaxSqlDbVersion":null,"MaximumInstanceCount":1,"ColorIndicator":null,"Severity":1,"Signature":{"ConditionID":"801f0803-c48d-413f-9af0-425dc3cc1200","VersionNumber":13,"AppliesToObjectTypeID":"0a11a887-823a-4461-87af-321cad1c3623","RuleDefinition":"{\"OperationTypeID\":\"6c3d38f1-9317-4258-972a-cfee0a0d76b5\",\"Children\":[{\"OperationTypeID\":\"8fa3cd6a-960c-43bb-96e0-94092fc2c296\",\"ValueDataTypeID\":\"b35a57f5-b8f6-4e4e-9380-e9d328e3eabf\",\"Left\":{\"ValueTypeID\":\"aa61ee90-8a95-4fdf-a208-b9a4afa26d9d\",\"Database\":\"master\",\"Query\":\"DECLARE @ProductVersion NVARCHAR(50);\\r\\nDECLARE @Major INT;\\r\\nDECLARE @NumaNodeCount INT;\\r\\nDECLARE @LogicalProcessorPerNumaNodeCount INT;\\r\\nDECLARE @EffectiveMaxDOP INT;\\r\\nDECLARE @LogicalProcessorThreshold INT;\\r\\nDECLARE @ResultMessage NVARCHAR(200);\\r\\nDECLARE @RecommendedMaxDOP INT = 0;\\r\\n\\r\\nSET @ProductVersion = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(50));\\r\\nSET @Major = CAST(LEFT(@ProductVersion, CHARINDEX('.', @ProductVersion)-1) AS INT);\\r\\n\\r\\nIF @Major < 13\\r\\nBEGIN\\r\\n\\tRAISERROR('This script is intended for SQL Server 2016 or higher. It will not work on version [%s].', 11, 1, @ProductVersion);\\r\\nEND\\r\\n\\r\\n-- Get the MAXDOP setting\\r\\nSELECT \\r\\n @EffectiveMaxDOP = CAST(value_in_use AS INT)\\r\\nFROM \\r\\n sys.configurations\\r\\nWHERE \\r\\n [name] = N'max degree of parallelism';\\r\\n\\r\\nIF @EffectiveMaxDOP = 0\\r\\nBEGIN\\r\\n\\tSELECT \\r\\n\\t\\t@EffectiveMaxDOP = COUNT(*)\\r\\n\\tFROM\\r\\n\\t\\tsys.dm_os_schedulers\\r\\n\\tWHERE \\r\\n\\t\\tscheduler_id <= 1048575\\r\\n\\t\\tAND is_online = 1;\\r\\nEND\\r\\n\\r\\n-- Get the NUMA node count\\r\\n-- Get the logical processors per numa node\\r\\nSELECT \\r\\n @NumaNodeCount = COUNT(*), @LogicalProcessorPerNumaNodeCount = MAX(online_scheduler_count)\\r\\nFROM \\r\\n sys.dm_os_nodes \\r\\nWHERE node_id <> 64; --Excluded DAC node\\r\\n\\r\\n--##TestPlaceholder##--\\r\\n\\r\\nIF @NumaNodeCount < 1 OR @LogicalProcessorPerNumaNodeCount < 1\\r\\nBEGIN\\r\\n RAISERROR('Could not capture NUMA node or logical processor count. Reported values - NUMA: [%d], Logical Processor: [%d]', \\r\\n 11,1, @NumaNodeCount, @LogicalProcessorPerNumaNodeCount);\\r\\nEND\\r\\n\\r\\nSET @LogicalProcessorThreshold = IIF(@NumaNodeCount = 1, 8, 16);\\r\\n\\r\\n--If NUMA = 1 and LogiProcs <= 8 THEN ASSERT(MaxDop <= LogiProcs)\\r\\n--If NUMA > 1 and LogiProcs <= 16 THEN ASSERT(MaxDop <= LogiProcs)\\r\\nIF @LogicalProcessorPerNumaNodeCount <= @LogicalProcessorThreshold\\r\\n AND @EffectiveMaxDOP > @LogicalProcessorPerNumaNodeCount\\r\\nBEGIN \\r\\n\\tSET @ResultMessage = N'MAXDOP should be less than or equal to the Logical Processor count per NUMA node.';\\r\\n\\tSET @RecommendedMaxDOP = @LogicalProcessorPerNumaNodeCount;\\r\\nEND\\r\\nELSE\\r\\nBEGIN\\r\\n\\t-- If NUMA = 1 and LogiProcs > 8 THEN ASSERT(MaxDop == 8)\\r\\n\\tIF @NumaNodeCount = 1 \\r\\n\\t\\tAND @LogicalProcessorPerNumaNodeCount > @LogicalProcessorThreshold\\r\\n\\t\\tAND @EffectiveMaxDOP <> @LogicalProcessorThreshold\\r\\n\\tBEGIN \\r\\n\\t\\tSET @ResultMessage = N'MAXDOP should be equal to 8.';\\r\\n\\t\\tSET @RecommendedMaxDOP = 8;\\r\\n\\tEND\\r\\n\\t-- If NUMA > 1 and LogiProcs > 16 THEN ASSERT(MaxDop <= 16 & MaxDop <= (LogiProcs / 2))\\r\\n\\tELSE\\r\\n\\tBEGIN\\r\\n\\t\\tIF @LogicalProcessorPerNumaNodeCount > @LogicalProcessorThreshold \\r\\n\\t\\t\\t\\tAND @EffectiveMaxDOP > @LogicalProcessorThreshold\\r\\n\\t\\tBEGIN \\r\\n\\t\\t\\tSET @ResultMessage = N'MAXDOP should not exceed a value of 16.';\\r\\n\\t\\t\\tSET @RecommendedMaxDOP = 16;\\r\\n\\t\\tEND\\r\\n\\t\\tELSE IF @LogicalProcessorPerNumaNodeCount > @LogicalProcessorThreshold \\r\\n\\t\\t\\t\\tAND (@LogicalProcessorPerNumaNodeCount / 2) <= @LogicalProcessorThreshold\\r\\n\\t\\t\\t\\tAND @EffectiveMaxDOP <> (@LogicalProcessorPerNumaNodeCount / 2)\\r\\n\\t\\tBEGIN \\r\\n\\t\\t\\tSET @ResultMessage = N'MAXDOP should be set at half the number of logical processors per NUMA node with a MAX value of 16.';\\r\\n\\t\\t\\tSET @RecommendedMaxDOP = (@LogicalProcessorPerNumaNodeCount / 2);\\r\\n\\t\\tEND\\r\\n\\tEND\\t\\r\\nEND\\r\\nIF @EffectiveMaxDOP = 1\\r\\nBEGIN\\r\\n\\tSET @RecommendedMaxDOP = CASE WHEN @LogicalProcessorPerNumaNodeCount <= @LogicalProcessorThreshold\\r\\n\\t\\t\\t\\t\\t\\t\\t\\t THEN @LogicalProcessorPerNumaNodeCount\\r\\n\\t\\t\\t\\t\\t\\t\\t\\t WHEN @LogicalProcessorPerNumaNodeCount > @LogicalProcessorThreshold \\r\\n\\t\\t\\t\\t\\t\\t\\t\\t AND (@LogicalProcessorPerNumaNodeCount / 2) <= @LogicalProcessorThreshold\\r\\n\\t\\t\\t\\t\\t THEN (@LogicalProcessorPerNumaNodeCount / 2)\\r\\n\\t\\t\\t\\t\\t\\t\\t\\t WHEN @LogicalProcessorPerNumaNodeCount > @LogicalProcessorThreshold\\r\\n\\t\\t\\t\\t\\t\\t\\t\\t THEN @LogicalProcessorThreshold\\r\\n\\t\\t\\t\\t\\t\\t\\t END;\\r\\n\\r\\n\\tRAISERROR('MAXDOP is set to 1, which suppresses parallel plan generation.', 0, 1);\\r\\nEND\\r\\n\\r\\nIF @ResultMessage IS NULL\\r\\nBEGIN\\r\\n\\tRAISERROR('MAXDOP setting is in the recommended range. Recommended value: [%d]. Reported values - NUMA: [%d], Logical Processor: [%d], MAXDOP: [%d]', \\r\\n\\t\\t\\t 0, 1, @RecommendedMaxDOP, @NumaNodeCount, @LogicalProcessorPerNumaNodeCount, @EffectiveMaxDOP);\\r\\nEND\\r\\nELSE\\r\\nBEGIN\\r\\n\\tRAISERROR('MAXDOP is not set based on recommendations in KB2806535. %s Recommended value: [%d]. Reported values - NUMA: [%d], Logical Processor: [%d], MAXDOP: [%d]', \\r\\n\\t\\t\\t 0, 1, @ResultMessage, @RecommendedMaxDOP, @NumaNodeCount, @LogicalProcessorPerNumaNodeCount, @EffectiveMaxDOP);\\r\\nEND\\r\\nSELECT @RecommendedMaxDOP;\",\"InstanceType\":0},\"ComparisonType\":2,\"Right\":{\"ValueTypeID\":\"07f87b7f-c063-47a2-a5be-772ba85ed827\",\"Value\":\"0\"},\"ID\":\"1\"}],\"BooleanOperationType\":0,\"ID\":\"0\"}","PublisherID":0,"PublishDateUtc":"2020-02-24T20:20:25","Rights":0,"SignatureVersion":1,"SignaturePublicKey":"uzJQ9gzevXFwOgw/hkcAtD+cA/bBbD1PRzhEZCxbZ6YwjJ1c9bbfXFItLQNwnm8bdWh2k57//qbpEj5DFHOW2EAjHc2Zw5m/vACm6OzelubPS5hbWvzshlaBJKm7KrpWQpPZClx/5eVvUVzOtlz+44RRTiOszObT58acJAQwA70=AQAB","Signature":"bg1MKRasKWMFNdEfGbFyLKy8Vn58OHOEL7xSUGE6hl9GP+fQspjZQnaleE7GfBWY8iJi11od9RJDB/CqXfnoKHLsE8nl6MstBUD46+qonURghqudFdyECYaaY1N2oinktnmtM0xJSqTi182LgXRr8UO6jd5twoj5+SAow9QBVwg=","IsSelfPublishedCondition":true},"Tags":null,"Areas":[],"ConditionSystemVersion":3,"MinDBSchemaVersion":null,"MaxDBSchemaVersion":null,"Items":[]}