sql-server - 优化包含窗口函数的参数化 T-SQL 查询的执行计划

Question

编辑:我已经更新了示例代码并提供了完整的表和 View 实现以供引用，但基本问题保持不变。

我在尝试查询的数据库中有一个相当复杂的 View 。当我尝试通过将 WHERE 子句硬编码为特定外键值来从 View 中检索一组行时， View 会以最佳执行计划(正确使用索引等)快速执行

SELECT * 
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = 20

但是，当我尝试向查询添加参数时，我的执行计划突然崩溃了。当我运行下面的查询时，我得到索引扫描而不是到处搜索，并且查询性能非常差。

DECLARE @ForeignKeyCol int = 20

SELECT * 
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = @ForeignKeyCol 

我使用的是 SQL Server 2008 R2。这里给出了什么？使用参数导致计划次优的原因是什么？任何帮助将不胜感激。

作为引用，以下是我收到错误的对象定义。

CREATE TABLE [dbo].[BaseTable]
(
    [PrimaryKeyCol] [uniqueidentifier] PRIMARY KEY,
    [ForeignKeyCol] [int] NULL,
    [DataCol] [binary](1000) NOT NULL
)

CREATE NONCLUSTERED INDEX [IX_BaseTable_ForeignKeyCol] ON [dbo].[BaseTable]
(
    [ForeignKeyCol] ASC
)

CREATE VIEW [dbo].[ViewOnBaseTable]
AS
SELECT
    PrimaryKeyCol,
    ForeignKeyCol,
    DENSE_RANK() OVER (PARTITION BY ForeignKeyCol ORDER BY PrimaryKeyCol) AS ForeignKeyRank,
    DataCol
FROM
    dbo.BaseTable

我确信窗口函数是问题所在，但我正在按窗口函数分区所依据的单个值来过滤查询，因此我希望优化器首先进行过滤，然后运行窗口函数。它在硬编码示例中执行此操作，但在参数化示例中则不然。下面是两个查询计划。上图好，下图不好。

Answer 1

使用选项(重新编译)时，请务必查看执行后(“实际”)计划，而不是执行前(“估计”)计划。有些优化仅在执行时应用:

DECLARE @ForeignKeyCol int = 20;

SELECT ForeignKeyCol, ForeignKeyRank
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = @ForeignKeyCol
OPTION (RECOMPILE);

预执行计划:

执行后计划:

在 SQL Server 2012 版本 11.0.3339 和 SQL Server 2008 R2 版本 10.50.4270 上进行测试

背景和限制

当 SQL Server 2005 中添加窗口函数时，优化器无法将选择推送到这些新的序列投影之上。为了解决一些导致性能问题的常见情况，SQL Server 2008 添加了一个新的简化规则 SelOnSeqPrj，该规则允许在值为常量的情况下推送合适的选择。该常量可以是查询文本中的文字，也可以是通过OPTION (RECOMPILE)获得的参数的嗅探值。尽管查询可能需要将 ANSI_NULLS OFF 才能看到这一点，但 NULLs 并没有特别的问题。据我所知，仅将简化应用于常量是一个实现限制；没有什么特殊原因不能将其扩展为处理变量。我记得 SelOnSeqPrj 规则解决了最常见的性能问题。

参数化

当查询成功自动参数化时，SelOnSeqPrj 规则不会应用。没有可靠的方法来确定 SSMS 中的查询是否已自动参数化，它仅表明已尝试自动参数化。需要明确的是，像 [@0] 这样的占位符的存在仅表明尝试了自动参数化。判断准备好的计划是否已缓存以供重用的可靠方法是检查计划缓存，其中“参数化计划句柄”提供临时计划和准备好的计划之间的链接。

例如，以下查询似乎在 SSMS 中自动参数化:

SELECT *
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = 20;

但计划缓存显示的情况并非如此:

WITH XMLNAMESPACES
(
    DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT
    parameterized_plan_handle =
        deqp.query_plan.value('(//StmtSimple)[1]/@ParameterizedPlanHandle', 'nvarchar(64)'), 
    parameterized_text =
        deqp.query_plan.value('(//StmtSimple)[1]/@ParameterizedText', 'nvarchar(max)'),
    decp.cacheobjtype,
    decp.objtype,
    decp.plan_handle
FROM sys.dm_exec_cached_plans AS decp
CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) AS deqp
WHERE
    dest.[text] LIKE N'%ViewOnBaseTable%'
    AND dest.[text] NOT LIKE N'%dm_exec_cached_plans%';

如果启用强制参数化的数据库选项，我们会得到参数化结果，但未应用优化:

ALTER DATABASE Sandpit SET PARAMETERIZATION FORCED;
DBCC FREEPROCCACHE;

SELECT *
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = 20;

计划缓存查询现在显示参数化缓存计划，由参数化计划句柄链接:

解决方法

如果可能，我的偏好是将 View 重写为内联表值函数，其中可以使选择的预期位置更加明确(如果需要):

CREATE FUNCTION dbo.ParameterizedViewOnBaseTable
    (@ForeignKeyCol integer)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT
        bt.PrimaryKeyCol,
        bt.ForeignKeyCol,
        ForeignKeyRank = DENSE_RANK() OVER (
            PARTITION BY bt.ForeignKeyCol 
            ORDER BY bt.PrimaryKeyCol),
        bt.DataCol
    FROM dbo.BaseTable AS bt
    WHERE
        bt.ForeignKeyCol = @ForeignKeyCol;

查询变为:

DECLARE @ForeignKeyCol integer = 20;
SELECT pvobt.*
FROM dbo.ParameterizedViewOnBaseTable(@ForeignKeyCol) AS pvobt;

执行计划: