迁移后验证和优化指南

适用于：SQL Server

SQL Server 迁移后步骤对于协调任何数据准确性和完整性至关重要，同时还能发现与工作负载相关的性能问题。

常见性能场景

以下是迁移到 SQL Server 平台后会遇到的一些常见性能方案及其解决方法。 其中包括从 SQL Server 迁移到 SQL Server（从较低版本迁移到较高版本），以及从外部平台（如 Oracle、DB2、MySQL 和 Sybase）迁移到 SQL Server 的方案。

由于基数估计器 (CE) 版本变更导致的查询回归

适用于：SQL Server 到 SQL Server 的迁移。

从较低版本的 SQL Server 迁移到 SQL Server 2014 (12.x) 或更高版本，并将数据库兼容性级别升级到最新可用级别时，工作负载可能会面临性能回归风险。

这是因为，自 SQL Server 2014 (12.x) 起，所有查询优化器更改都会绑定到最新的数据库兼容性级别，因此计划不会在升级后立即更改，而是在用户将 COMPATIBILITY_LEVEL 数据库更改为最新版本后更改。 利用此功能和 Query Store，你可以在升级过程中对查询性能进行精确的控制。

若要详细了解 SQL Server 2014 (12.x) 中引入的查询优化器更改，请参阅使用 SQL Server 2014 基数估算器优化查询计划。

有关 CE 的详细信息，请参阅基数估计 (SQL Server)。

解决步骤

将数据库兼容性级别更改为源版本，并遵循下图中推荐的升级工作流：

有关本文的详细信息，请参阅在升级到更高版本 SQL Server 的过程中保持性能稳定性。

对参数截取的敏感性

适用于：从外部平台（如 Oracle、DB2、MySQL 和 Sybase）到 SQL Server 的迁移。

SQL Server 可针对存储过程编译查询计划，方法是在首次编译时截取输入参数，生成参数化且可重复使用的计划，然后对该输入数据分发进行优化。 即使未使用存储过程，生成普通计划的大多数据语句仍会被参数化。 缓存计划后，任何后期执行都会映射到之前缓存的计划中。

如果首次编译未使用适用于常用工作负载的最常用参数集，可能会出现潜在问题。 对不同的参数使用相同的执行计划可能导致效率低下。 有关本文的详细信息，请参阅参数敏感度。

解决步骤

1. 使用 RECOMPILE 提示。 每次基于一个参数值对计划进行调整时，都会对计划进行计算。

2. 重写存储过程以使用 (OPTIMIZE FOR(<input parameter> = <value>)) 选项。 确定并使用最适合相关工作负载的值，同时创建并维护可基于参数化值变得更高效的计划。

3. 使用过程中的本地变量重写存储过程。 现在，优化器可使用密度向量进行预估，无论使用什么参数值都将生成相同计划。

4. 重写存储过程以使用 (OPTIMIZE FOR UNKNOWN) 选项。 与使用本地变量方法的效果相同。

5. 使用 DISABLE_PARAMETER_SNIFFING 提示重写查询。 完全禁用参数截取与使用本地变量方法的效果相同（除非使用 OPTION(RECOMPILE)、WITH RECOMPILE 或 OPTIMIZE FOR <value>）。

缺失索引

适用于：从外部平台（如 Oracle、DB2、MySQL 和 Sybase）和 SQL Server 到 SQL Server 的迁移。

不正确的索引或缺失索引会导致额外的 I/O，从而产生额外内存并浪费 CPU。 原因可能是工作负载配置文件已更改（例如使用了其他谓词），进而导致现有索引设计无效。 索引策略不佳或工作负载配置文件发生更改的证据包括：

• 查找重复、冗余、很少使用及完全未使用过的索引。
• 特别注意有更新但未使用过的索引。

解决步骤

1. 将图形执行计划用于任何“缺失索引”引用。

2. 数据库引擎优化顾问生成的索引建议。

3. 使用 sys.dm_db_missing_index_details。

4. 使用预先存在的脚本，这些脚本可以使用现有的 DMV 来深入了解数据库中现有过程和函数中的任何缺失、重复、冗余、很少使用且完全未使用的索引，但也可能会提示/硬编码到数据库中的现有过程和函数。

无法使用谓词筛选数据

适用于：从外部平台（如 Oracle、DB2、MySQL 和 Sybase）和 SQL Server 到 SQL Server 的迁移。

SQL Server 查询优化器仅适用于编译时已知的信息。 如果工作负荷依赖于仅可在执行时已知的谓词，则选择不适合的计划的可能性会增加。 对于质量更好的计划，谓词必须是 SARGable。

非 SARGable 谓词的一些示例：

• 隐式数据转换，例如从 varchar 转换到 nvarchar，或从 int 转换到 varchar。 查找实际执行计划中的运行时 CONVERT_IMPLICIT 警告。 从一种类型转换到另一种类型还会导致精度损失。

• 不确定性的复杂表达式，如 WHERE UnitPrice + 1 < 3.975，而不是 WHERE UnitPrice < 320 * 200 * 32。

• 使用函数的表达式，如 WHERE ABS(ProductID) = 771 或 WHERE UPPER(LastName) = 'Smith'

• 以通配符开头的字符串，如 WHERE LastName LIKE '%Smith'，而不是 WHERE LastName LIKE 'Smith%'。

解决步骤

1. 始终将变量/参数声明为所需目标数据类型。

   这可能涉及将存储在数据库中的任何用户定义的代码构造（例如存储过程、用户定义的函数或视图）与保存基础表（如 sys.columns）中使用的数据类型的信息的系统表进行比较。

2. 如果无法将所有代码遍历到前一个点，则出于相同的目的，请更改表中的数据类型，使其匹配任何变量/参数声明。

3. 了解以下结构的用途：

   • 用作谓词的函数；
   • 通配符搜索；
   • 基于列数据的复杂表达式 - 评估是否需要改为创建可以索引的持久化计算列；

使用表值函数（多语句表值函数和内联表值函数）

适用于：从外部平台（如 Oracle、DB2、MySQL 和 Sybase）和 SQL Server 到 SQL Server 的迁移。

表值函数返回可作为视图替代项的表数据类型。 视图仅限为单个 SELECT 语句，而用户定义函数可包含更多语句，与视图相比，这些语句支持更多逻辑。

由于多语句表值函数（MSTVF）的输出表未在编译时创建，SQL Server 查询优化器依赖于启发式（而不是实际统计信息）来确定行估计。

即使索引已添加到基表，这也无济于事。

对于 MSTVF，SQL Server 对于 MSTVF 预期返回的行数使用固定估算值 1（从 SQL Server 2014 (12.x) 开始，该固定估算值为 100 行）。

解决步骤

1. 如果 MSTVF 只是单个语句，转换为内联表值函数。

   CREATE FUNCTION dbo.tfnGetRecentAddress (@ID INT)
   RETURNS
       @tblAddress TABLE ([Address] VARCHAR (60) NOT NULL)
   AS
   BEGIN
       INSERT INTO @tblAddress ([Address])
       SELECT TOP 1 [AddressLine1]
       FROM [Person].[Address]
       WHERE AddressID = @ID
       ORDER BY [ModifiedDate] DESC;
       RETURN;
   END
   

   接下来显示内联格式示例。

   CREATE FUNCTION dbo.tfnGetRecentAddress_inline
   (@ID INT)
   RETURNS TABLE
   AS
   RETURN
       (SELECT TOP 1 [AddressLine1] AS [Address]
        FROM [Person].[Address]
        WHERE AddressID = @ID
        ORDER BY [ModifiedDate] DESC)
   

2. 如果问题更复杂，请考虑使用内存优化表或临时表中存储的中间结果。

相关内容

• 使用查询存储监视工作负载的最佳做法
• 内存中 OLTP 的示例数据库
• 用户定义的函数
• 表变量和行预估 - 第 1 部分
• 表变量和行预估 - 第 2 部分
• 执行计划的缓存和重新使用