确定有问题的查询计划

DBA 对查询性能进行故障排除的典型方法首先涉及识别有问题的查询，通常是消耗大多数系统资源的查询，然后检索其执行计划。 有两个主要方案。 一种情况是查询一直表现不佳。 这可能是由于各种问题（例如硬件资源约束（尽管这通常不会影响单独运行的单个查询）、次优查询结构、数据库兼容性设置、缺少索引或查询优化器计划选项不佳。 第二种情况是查询在某些执行中表现良好，但在其他执行中表现不佳。 这种不一致可能是由参数化查询中的数据偏斜等因素引起的，该参数化查询具有一些执行的有效计划，而其他执行计划很差。 其他常见因素包括阻塞，即一个查询等待另一个查询完成以获取对表的访问权限或硬件争用。

让我们更详细地探讨其中每个方案。

硬件约束

硬件约束通常不会在单个查询执行期间出现，但在 CPU 线程和内存受限时，在生产负载下变得明显。 可以通过观察性能监视器计数器“% 处理器时间”来检测 CPU 争用，该计数器度量服务器 CPU 使用率。 在 SQL Server 中， SOS_SCHEDULER_YIELD 和 CXPACKET 等待类型可以指示 CPU 压力。 存储系统性能不佳甚至会降低优化单个查询执行的速度。 跟踪存储性能的最佳方式是使用性能监视器计数器 Disk Seconds/Read 和 Disk Seconds/Write 在操作系统级别进行跟踪，这两个计数器可度量 I/O 操作完成时间。 如果 I/O 花费的时间超过 15 秒，SQL Server 会记录存储性能不佳的情况。 SQL Server 中的 PAGEIOLATCH_SH 等待较高可能指示存储性能问题。 硬件性能通常在故障排除过程中的早期进行评估，因为它易于评估。

大多数数据库性能问题源于欠佳的查询模式，这可能会给硬件造成不正当的压力。 例如，缺少索引可能会导致 CPU、存储和内存压力，因为会检索超过必要的数据。 建议在解决硬件问题之前解决和优化欠佳查询。 接下来，我们将了解查询优化。

欠佳查询构造

关系数据库在执行基于集的操作时表现最佳，这种操作是在集合中操作数据（INSERT、UPDATE、DELETE 和 SELECT），从而生成单个值或结果集。 替代方法是基于行的处理，使用游标或循环，这会随着受影响的行数以线性方式增加成本 — 随着数据量的增大，这种方法的扩展会出现问题。

使用游标或 WHILE 循环检测基于行的操作的不理想使用非常重要，但还有其他 SQL Server 反模式需要识别。 表值函数（TVF），尤其是多语句 TVF，在 SQL Server 2017 之前导致了有问题的执行计划模式。 开发人员通常使用多语句 TVF 在单个函数中执行多个查询，并将结果聚合到单个表中。 但是，使用 TVF 可能会导致性能损失。

SQL Server 有两种类型的 TVF：内联和多语句。 在查询处理过程中，内联 TVF 像视图一样处理，而多语句 TVF 则像表一样处理。 由于 TVF 是动态的且缺少统计信息，因此 SQL Server 使用固定行计数来估算查询计划成本。 对于行数较小的情况，这没有问题，但对于成千上万行或数百万行，这会非常低效。

另一种反模式是使用标量函数，这些函数具有类似的估计和执行问题。 Microsoft通过在兼容级别为140和150的情况下，提升智能查询处理的性能，取得了显著的改进。

SARGability

在关系数据库中，“SARGable”这个术语是指一种能够利用索引来加快查询执行速度的谓词（WHERE 子句）。 格式正确的谓词称为“搜索参数”或 SARG。 在 SQL Server 中，使用 SARG 意味着优化器使用 SARG 中引用的列上的非聚集索引进行 SEEK 操作，而不是扫描整个索引或表来检索值。

SARG 的存在不保证使用索引进行 SEEK 操作。 优化器的成本算法仍可以确定索引过于昂贵，尤其是在 SARG 引用表中较大比例的行时。 缺少 SARG 意味着优化器不会在非聚集索引上评估 SEEK。

非 SARGable 表达式的示例包括那些带有在字符串开头使用通配符的 LIKE 子句，例如 WHERE lastName LIKE '%SMITH%'。 在列上使用函数时，会出现其他非 SARG 谓词，例如 WHERE CONVERT(CHAR(10), CreateDate,121) = '2020-03-22'。 这些查询通常通过检查索引或表扫描的执行计划来标识，这些位置本应发生搜寻操作。

使用非易于搜索函数的查询和执行计划的屏幕截图。

“City”列上有一个索引，该索引在查询的 子句中使用，并且在上述执行计划中使用它时，可以看到正在扫描该索引，这意味着整个索引正在被读取WHERE。 谓词中的 LEFT 函数使此表达式成为非 SARGable 表达式。 优化器将不会使用索引搜索对“City”列上的索引进行计算。

此查询可以编写为使用 SARGable 的谓词。 然后，优化器将对 City 列上的索引进行 SEEK 评估。 在本例中，索引查找运算符将读取较小的行集。

具有 SARGable 谓词的查询和执行计划的屏幕截图。

将 LEFT 函数更改为 LIKE 会导致索引查找。

其他一些数据库开发反模式将数据库视为服务，而不是数据存储。 使用数据库将数据转换为 JSON、作字符串或执行复杂的计算可能会导致 CPU 使用率过高和延迟增加。 尝试检索所有记录，然后在数据库中执行计算的查询可能会导致 IO 和 CPU 使用率过高。 理想情况下，应使用数据库进行数据访问操作，并使用优化的数据库结构，例如聚合。

缺失索引

数据库管理员最常见的性能问题源于缺少有用的索引，导致引擎读取的页数超过返回查询结果所需的页数。 虽然索引消耗资源（影响写入性能和占用空间），但其性能提升通常超过额外的资源成本。 查询运算符 聚集索引扫描 或 非聚集索引查找 和 键查找的组合可以识别这些问题的执行计划，指示现有索引中缺少的列。

数据库引擎通过报告执行计划中缺少的索引来提供帮助。 建议索引的名称和详细信息可通过动态管理视图 sys.dm_db_missing_index_details获得。 其他 DMV（如 sys.dm_db_index_usage_stats 和 sys.dm_db_index_operational_stats）突出显示现有索引的利用率。

删除未使用的索引可能合理。 缺少索引 DMV 和计划警告应该是优化查询的起点。 了解关键查询并生成索引以支持它们至关重要。 不建议在上下文中创建所有缺失的索引，而无需对其进行评估。

缺失和过期统计信息

了解列和索引统计信息对查询优化器的重要性至关重要。 还必须识别可能导致过时统计信息的条件，以及此问题如何在 SQL Server 中显示。 Azure SQL 产品/服务默认将自动更新统计信息设置为 ON。 在 SQL Server 2016 之前，自动更新统计信息的默认行为是在索引中对列的修改数等于表中行数约 20% 之前，才会更新统计信息。 此行为可能会导致重大数据修改，从而在不更新统计信息的情况下更改查询性能，从而导致基于过时统计信息的欠佳计划。

在 SQL Server 2016 之前，跟踪标志 2371 可用于将所需修改数更改为动态值，因此随着表的增长，触发统计信息更新所需的行修改百分比会减少。 默认情况下，较新版本的 SQL Server、Azure SQL 数据库和 Azure SQL 托管实例支持此行为。 动态管理功能 sys.dm_db_stats_properties 显示上次更新统计信息的时间和自上次更新以来的修改次数，使你能够快速识别可能需要手动更新的统计信息。

优化器选项不佳

尽管查询优化器能够很好地优化大多数查询，但在某些情况下，基于成本的优化器可以做出无法完全理解的有影响力的决策。 有多种方法可以解决此问题，包括使用查询提示、跟踪标志、执行计划强制和其他调整，以实现稳定且最佳的查询计划。 Microsoft有一个支持团队，可帮助排查这些情况。

在 AdventureWorks2017 数据库的以下示例中，查询提示用于指示数据库优化器始终使用城市名“西雅图”。 此提示不能保证所有城市值的最佳执行计划，但它是可预测的。 @city_name 的“Seattle”值将仅在优化期间使用。 在执行期间，将使用实际提供的值 (‘Ascheim’) 。

DECLARE @city_name nvarchar(30) = 'Ascheim',
        @postal_code nvarchar(15) = 86171;

SELECT * 
FROM Person.Address
WHERE City = @city_name 
      AND PostalCode = @postal_code
OPTION (OPTIMIZE FOR (@city_name = 'Seattle');

如示例中所示，查询使用提示（#D0 子句）告诉优化器使用特定的变量值来生成其执行计划。

参数探查

SQL Server 缓存查询执行计划以供将来使用。 由于执行计划检索过程基于查询的哈希值，因此每次执行缓存计划的查询时，查询文本必须相同。 为了支持同一查询中的多个值，许多开发人员使用通过存储过程传入的参数，如以下示例所示：

CREATE PROC GetAccountID (@Param INT)
AS

<other statements in procedure>

SELECT accountid FROM CustomerSales WHERE sales > @Param;

<other statements in procedure>

RETURN;

-- Call the procedure:

EXEC GetAccountID 42;

还可以使用过程 sp_executesql 显式参数化查询。 但是，使用 PREPARE 和 EXECUTE 的某种形式（具体取决于 API）通过应用程序对单个查询进行显式参数化。 当数据库引擎首次执行该查询时，它会根据参数的初始值（在本例中为 42）优化查询。 此行为称为参数探查，允许在服务器上减少编译查询的总体工作负荷。 但是，如果存在数据偏差，查询性能可能会有所不同。

例如，具有 1000 万条记录的表，其中 99% 条记录的 ID 为 1，其他 1 个% 是唯一的数字，性能基于最初用于优化查询的 ID。 这种剧烈波动的性能表明数据分布不均，并不是参数嗅探的固有问题。 此行为是你应该注意的一个相当常见的性能问题。 你应该了解缓解问题的选项。 有几种方法可以解决此问题，但它们都有利弊：

• 在查询中使用 RECOMPILE 提示，或在存储过程中使用 WITH RECOMPILE 执行选项。 此提示会导致每次执行查询或过程时重新编译，这将增加服务器上的 CPU 使用率，但始终使用当前参数值。
• 可使用 OPTIMIZE FOR UNKNOWN 查询提示。 此提示会导致优化器选择不嗅探参数，并将值与列数据直方图进行比较。 此选项不会让你获得最佳的计划，但将允许一致的执行计划。
• 通过在参数值两侧添加逻辑来重写你的过程或查询，以便仅重新编译已知具有问题的参数。 在下面的示例中，如果 SalesPersonID 参数为 NULL，则使用 OPTION (RECOMPILE) 执行查询。

CREATE OR ALTER PROCEDURE GetSalesInfo (@SalesPersonID INT = NULL)
AS
DECLARE  @Recompile BIT = 0
         , @SQLString NVARCHAR(500)

SELECT @SQLString = N'SELECT SalesOrderId, OrderDate FROM Sales.SalesOrderHeader WHERE SalesPersonID = @SalesPersonID'

IF @SalesPersonID IS NULL
BEGIN
     SET @Recompile = 1
END

IF @Recompile = 1
BEGIN
    SET @SQLString = @SQLString + N' OPTION(RECOMPILE)'
END

EXEC sp_executesql @SQLString
    ,N'@SalesPersonID INT'
    ,@SalesPersonID = @SalesPersonID
GO

此示例是一个很好的解决方案，但它确实需要相当大的开发工作，并且对数据分布有坚定的理解。 当数据发生更改时，它需要维护。