如何提升SQL Server跨可用性组同步只读库与普通库的查询性能

• 统计信息读取权限不足：你仅拥有a库的只读权限，默认没有VIEW DATABASE STATE权限，SQL Server优化器在b库执行跨库查询时，无法获取a库的最新统计信息，只能使用默认的粗糙基数估算，生成的执行计划完全不符合实际数据分布，这是最常见的原因。
• 执行上下文配置不匹配：如果a库和b库的数据库兼容性级别、基数估算器版本、MAXDOP等配置参数不一致，在b库执行查询时优化器会使用b库的配置生成计划，和在a库执行时的计划差异极大。
• 跨库元数据访问限制：优化器在b库执行时无法获取a库的索引元数据细节，会误判a库不存在对应索引，因此才会弹出缺失索引建议，实际a库的索引是存在的，只是优化器看不到。

1. 优先调整统计信息读取权限 #

这是成本最低效果最好的方案，仅需给你的查询账号授予a库的VIEW DATABASE STATE权限，该权限仅允许读取数据库统计信息、元数据，不会开放写入权限，符合你仅拥有a库只读权限的要求，执行命令如下：

USE a;
GRANT VIEW DATABASE STATE TO [你的查询登录账号];

授权完成后执行以下命令清空对应执行计划缓存，重新执行查询即可获得和a库一致的高效执行计划：

USE b;
DBCC FREEPROCCACHE; -- 可按需指定具体计划句柄清空，避免影响全库

2. 查询逻辑拆分优化 #

如果权限调整受限无法操作，可将复杂跨库查询拆分为多步，先把a库的表数据按过滤条件拉取到b库的临时表，再在临时表上做关联查询，避免优化器跨库生成错误执行计划：

-- 先拉取a库所需数据到b库临时表
SELECT col_a, t2_id INTO #t1 FROM a.dbo.Table1;
SELECT Id, col_b INTO #t2 FROM a.dbo.Table2;
-- 本地临时表关联计算，性能稳定
SELECT t1.col_a, t2.col_b
FROM #t1 t1
LEFT JOIN #t2 t2 ON t1.t2_id = t2.Id;

3. 增加查询提示强制正确优化 #

如果不想拆分逻辑，可在查询末尾增加优化提示，强制优化器使用正确的基数估算规则：

SELECT
    t1.col_a,
    t2.col_b
FROM
    a.dbo.Table1 t1
LEFT OUTER JOIN
    a.dbo.Table2 t2 on t1.t2_id = t2.Id
-- 提示优化器重编译，且使用SQL Server 2016默认基数估算器（和a库对齐即可）
OPTION (RECOMPILE, USE HINT ('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_130'));

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