SQL Server 2014中如何非动态SQL实现用变量/表值设置MAXDOP？

你提到的痛点确实很典型：SQL Server 2014及更早版本里，OPTION (MAXDOP @int)这种直接用变量指定并行度的写法不被支持，而动态SQL又会带来执行计划缓存碎片化、维护成本高等问题。下面给你几个不用改业务代码，就能从配置表读取值来动态控制MAXDOP的可行方案：

方案一：计划指南（Plan Guides）+ 配置表 #

计划指南可以帮你给特定查询绑定查询选项（包括MAXDOP），你只需要把MAXDOP的配置存在表中，再定期更新计划指南就能生效，完全不用碰业务代码。

具体步骤： #

1. 创建配置表：

CREATE TABLE dbo.QueryMaxDopConfig (
    BusinessKey NVARCHAR(100) PRIMARY KEY, -- 用来标识业务，比如Purchases
    TargetMaxDop INT NOT NULL CHECK (TargetMaxDop BETWEEN 1 AND 64)
);
-- 初始化采购业务的MAXDOP配置
INSERT INTO dbo.QueryMaxDopConfig VALUES ('Purchases_Count', 1);

2. 为目标查询创建计划指南：
   要确保计划指南的查询文本和业务中的查询完全匹配（包括空格、大小写，或者用模糊匹配的计划指南）：

DECLARE @currentMaxDop INT;
SELECT @currentMaxDop = TargetMaxDop FROM dbo.QueryMaxDopConfig WHERE BusinessKey = 'Purchases_Count';

EXEC sp_create_plan_guide
    @name = N'PG_Purchases_Count_MaxDop',
    @stmt = N'SELECT COUNT(*) AS MyCount FROM dbo.TableName',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION (MAXDOP ' + CAST(@currentMaxDop AS NVARCHAR(10)) + N')';

3. 自动更新计划指南：
   创建一个SQL Server代理作业，或者给配置表加触发器，当配置值变化时自动重建计划指南：

-- 更新脚本示例
DECLARE @newMaxDop INT;
SELECT @newMaxDop = TargetMaxDop FROM dbo.QueryMaxDopConfig WHERE BusinessKey = 'Purchases_Count';

-- 先删除旧的计划指南
EXEC sp_control_plan_guide N'DROP', N'PG_Purchases_Count_MaxDop';

-- 创建新的计划指南
EXEC sp_create_plan_guide
    @name = N'PG_Purchases_Count_MaxDop',
    @stmt = N'SELECT COUNT(*) AS MyCount FROM dbo.TableName',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION (MAXDOP ' + CAST(@newMaxDop AS NVARCHAR(10)) + N')';

优点：完全不影响业务代码，配置变更后自动生效；缺点：需要确保计划指南的查询文本和业务查询严格匹配，业务查询变更时要同步更新计划指南。

方案二：资源调控器（Resource Governor） #

如果你的Purchases等业务可以通过连接属性（比如应用程序名称、登录名）区分，资源调控器是更优雅的批量控制方案——它可以给整个业务组设置MAXDOP，从配置表读值后动态调整资源池即可。

具体步骤： #

1. 创建资源池配置表：

CREATE TABLE dbo.ResourcePoolConfig (
    PoolName NVARCHAR(100) PRIMARY KEY,
    PoolMaxDop INT NOT NULL CHECK (PoolMaxDop BETWEEN 1 AND 64)
);
INSERT INTO dbo.ResourcePoolConfig VALUES ('Purchases_Pool', 1);

2. 创建资源池和工作负载组：

-- 创建专属资源池
CREATE RESOURCE POOL Purchases_Pool WITH (MAX_DOP = 1);
-- 创建对应工作负载组
CREATE WORKLOAD GROUP Purchases_Group USING Purchases_Pool;
GO

3. 编写分类函数：
   定义如何把Purchases业务的请求分配到专属工作负载组，比如通过应用程序名称判断：

CREATE FUNCTION dbo.PurchasesClassifier() 
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    -- 根据你的业务实际标识调整判断条件
    IF APP_NAME() = 'Purchases_Application'
        RETURN N'Purchases_Group';
    -- 其他请求走默认组
    RETURN N'default';
END;
GO
-- 注册分类函数并生效
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.PurchasesClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

4. 定期更新资源池MAXDOP：
   创建SQL Server代理作业，定期从配置表读取值并更新资源池：

DECLARE @targetMaxDop INT;
SELECT @targetMaxDop = PoolMaxDop FROM dbo.ResourcePoolConfig WHERE PoolName = 'Purchases_Pool';

ALTER RESOURCE POOL Purchases_Pool WITH (MAX_DOP = @targetMaxDop);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

优点：可以批量控制一类业务的并行度，不用关注单个查询；缺点：需要提前规划好业务的分类规则，对环境配置有一定要求。

额外提醒 #

• 无论用哪种方案，都要确保MAXDOP的值符合SQL Server最佳实践：比如逻辑CPU数≤8时设为CPU数，超过8时设为8，NUMA架构下不超过单个NUMA节点的CPU数。
• 配置变更后，可以通过sys.dm_exec_query_plan查看执行计划的DegreeOfParallelism属性，验证MAXDOP是否生效。

内容的提问来源于stack exchange，提问作者Etienne