主页 / dba / 问题 / 345103
Accepted
jkohl
Asked: 2025-02-05 20:11:47 +0800 CST

访问 SQL Server 中视图的参数化查询中的性能问题

  • 772

我目前面临 SQL Server 中参数化查询的问题，我不知道它的根源在哪里。

我将其分解为一个简单的例子：

假设有一张表，其中包含有关某个子实体的数据以及 ，以及parent_id上的相应索引parent_id。基于此访问数据，parent_id但通过视图访问，该视图除了表数据外，还包含一列，用于计算row_number按 分区的所有条目parent_id。

可重复的设置

创建表、索引和视图如下：

CREATE TABLE dbo.test (id BIGINT IDENTITY(1,1), text NVARCHAR(255), parent_id BIGINT);
GO

CREATE NONCLUSTERED INDEX idx_test_parent_id
       ON dbo.test (parent_id);
GO

CREATE VIEW dbo.test_view
AS
  SELECT *, ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY id) AS row_num
  FROM dbo.test
GO

现在将一些数据放入表中：

DECLARE @i BIGINT = 0
WHILE @i < 200000
BEGIN
    SET @i = @i + 1
    INSERT INTO dbo.test (text, parent_id)
    VALUES ('test 1', @i), ('test 2', @i), ('test 3', @i);
END

问题

当通过视图中的参数化查询访问数据时，SQL Server 将对表进行完整扫描。

DECLARE @parent_id BIGINT = 123456
SELECT *
FROM dbo.test_view
WHERE parent_id = @parent_id

查询计划全表扫描

而当直接访问数据（不使用参数）时，我们将得到预期的索引查找。

SELECT *
FROM dbo.test_view
WHERE parent_id = 123456

查询计划索引查找

我尝试过

搜索不同的论坛，我不太明白这里发生了什么。我发现了类似的问题，参数的数据类型错误，因此性能很差，但这对我来说不是问题。我也读过有关参数嗅探的问题，但我不认为这是问题，因为我不通过存储过程或函数访问数据。

此外，当我使用参数化查询直接从表中访问数据时，不会发生此问题。即使使用参数，也会进行索引查找。

当我将OPTION (RECOMPILE)查询添加到使用参数化查询访问视图的查询时，也会发生同样的情况，SQL Server 最终会执行索引查找。

问题

有人能解释一下这里的问题吗？为什么这是视图的问题而不是表本身的问题？我真的需要row_number在插入/删除期间摆脱以不同方式计算的视图吗？

设置

  • 在 Docker 容器中运行的 SQL Server 2022 v16.0.4165
  • Docker 映像：mcr.microsoft.com/mssql/server:2022-latest

当然，实际的表有一个主键。但它也有很多列，而不仅仅是text列。将所有这些列都包含在索引中是可能的。不过，从表本身进行选择时不会出现此问题，所以在我看来，这似乎不是索引的问题。

我不知道我正在以兼容模式运行数据库。在生产环境中我甚至得到了CardinalityEstimationModelVersion="140"。我不认为我故意在任何地方设置了它。

执行计划

sql-server

3 个回答

  1. Best Answer

    您的执行计划显示您正在以CardinalityEstimationModelVersion150（相当于 SQL Server 2019）的速度运行。

    你说你的生产计划使用 140 (SQL Server 2017)

    我还可以通过设置数据库兼容级别 140 或 150 在 SQL Server 2022 上重现此问题。

    看起来您遇到了Paul White 撰写的此 Stack Overflow 答案和窗口函数和视图的问题SelOnSeqPrj中描述的问题。

    当兼容性级别 (CL) 140 和 150 时，该问题就会消失ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON（这是有道理的，因为它已在SQL Server 2017 的 CU30和SQL Server 2019 的 CU17中修复），并且无论配置选项如何，它都不会在 CL 160 上重现。

    在此处输入图片描述

    如果您使用的是 2022 版本并且只是无意中使用了较旧的 CL，那么您应该考虑更改它以获取最新和最好的功能并且默认修复此问题 - 而无需启用QUERY_OPTIMIZER_HOTFIXES。

    不过，更改兼容级别确实需要测试，因为有时级别之间会发生重大行为变化，并且不同的基数估计模型会对执行计划产生积极或消极的影响。您可以使用查询存储来帮助降低第二个问题的风险。

    • 5

  2. 您正在使用局部变量。与硬编码值或参数化查询（准备好的语句或存储过程）中的参数不同，局部变量在编译计划时不会使用提供的值来查看统计数据。相反，它使用统计数据中的值的平均值来制定计划。除非出现重新编译的情况。然后，可以使用局部变量中的值来查看统计数据的具体信息，而不是平均值，以得出不同的行数。

    这几乎描述了您所看到的所有行为。通过从查询中创建存储过程然后传入相同的值来测试它。

    现在，话虽如此，与之相反的是，对特定值进行采样（也称为参数嗅探）可以为您提供更准确的行数，从而获得更好的执行计划。直到您发现某些参数返回的行数多于（或少于）用于创建计划的指定行数。然后，性能可能会很糟糕，因为您要么需要基于行平均值的计划，要么需要针对每个可能值的特定计划。在这里，您会发现自己每次都要处理查询提示 OPTIMIZE FOR 或 OPTIMIZE FOR UNKNOWN，或者添加 RECOMPILE 提示以获取特定计划。SQL Server 2022 及更高版本甚至具有所谓的参数敏感计划优化来帮助处理这种情况。

    简而言之，这一切都变得困难。

    • 3

  3. 虽然您已经确定了参数嗅探问题，但真正的解决方案可能不是向您的查询添加提示。当您让服务器在糟糕的计划和更糟糕的计划之间做出选择，而它却没有选择正确的计划时，就会发生参数嗅探问题。相反，您需要给它一个很好的选择，以便它总是选择正确的计划。

    因此您需要改进索引。首先，您缺少主键，这在正确规范化的数据库中是大忌。

    ALTER TABLE dbo.test
  ADD PRIMARY KEY (id);

    然后，为了解决您的实际问题，您需要用索引“覆盖”INCLUDE查询列。因此您需要列。

    CREATE NONCLUSTERED INDEX idx_test_parent_id
       ON dbo.test (parent_id, id) INCLUDE (text)
  WITH (DROP_EXISTING = ON);

    现在，您的查询计划非常简洁，即使使用了局部变量。

    查询计划

    db<>小提琴

    • 1

相关问题