SQL Server动态SQL传递DateTime2如何利用索引Seek？

问题背景 #

原存储过程搭配OPTION(RECOMPILE)使用时，能借助CreatedDate字段上的索引执行Seek操作，高效定位分页数据。但改用动态SQL后，查询变为Scan操作，读取行数远超预期，无法支撑百万级数据量的性能需求。

原带RECOMPILE的存储过程：

CREATE PROCEDURE [dbo].[GetPageOfMultipartDataFeedItems]
    @NumberOfItems INT, 
    @FromCreatedDate DATETIME2(7) = NULL
AS
BEGIN
    
    SET NOCOUNT ON;

    SELECT DataKey, FeedData, CreatedDate
    FROM MultipartDataFeed
    WHERE (@FromCreatedDate IS NULL OR CreatedDate > @FromCreatedDate)
    AND IsActive = 1
    ORDER BY CreatedDate ASC
    OFFSET 0 ROWS
    FETCH NEXT @NumberOfItems ROWS ONLY
    OPTION(RECOMPILE)
END

改写后出现Scan问题的动态SQL存储过程：

ALTER PROCEDURE [dbo].[GetPageOfMultipartDataFeedItems]
    @NumberOfItems INT, 
    @FromCreatedDate DATETIME2(7) = NULL
AS
BEGIN
    
    DECLARE @StringToExecute NVARCHAR(4000);

    SET @StringToExecute = N'/* GetPageOfMultipartDataFeedItems */
        SELECT DataKey, FeedData, CreatedDate
        FROM MultipartDataFeed
        WHERE (@FromCreatedDate IS NULL OR CreatedDate > @FromCreatedDate)
        AND IsActive = 1
        ORDER BY CreatedDate ASC
        OFFSET 0 ROWS
        FETCH NEXT @NumberOfItems ROWS ONLY
    ';

    EXEC sp_executesql @StringToExecute, 
        N'@NumberOfItems INT, 
        @FromCreatedDate DATETIME2(7) = NULL',
        @NumberOfItems, @FromCreatedDate;
END

解决方案 #

核心思路是动态生成匹配参数状态的WHERE条件，避免SQL Server因可选参数的模糊条件无法选择最优执行计划。具体根据@FromCreatedDate是否为NULL，生成两种明确的SQL分支：

修改后的动态SQL存储过程 #

ALTER PROCEDURE [dbo].[GetPageOfMultipartDataFeedItems]
    @NumberOfItems INT, 
    @FromCreatedDate DATETIME2(7) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @StringToExecute NVARCHAR(4000);

    -- 根据参数状态生成精准查询语句
    IF @FromCreatedDate IS NULL
    BEGIN
        SET @StringToExecute = N'
            SELECT DataKey, FeedData, CreatedDate
            FROM MultipartDataFeed
            WHERE IsActive = 1
            ORDER BY CreatedDate ASC
            OFFSET 0 ROWS
            FETCH NEXT @NumberOfItems ROWS ONLY
        ';
    END
    ELSE
    BEGIN
        SET @StringToExecute = N'
            SELECT DataKey, FeedData, CreatedDate
            FROM MultipartDataFeed
            WHERE CreatedDate > @FromCreatedDate
              AND IsActive = 1
            ORDER BY CreatedDate ASC
            OFFSET 0 ROWS
            FETCH NEXT @NumberOfItems ROWS ONLY
        ';
    END

    -- 参数化执行动态SQL，避免注入并缓存计划
    EXEC sp_executesql @StringToExecute, 
        N'@NumberOfItems INT, @FromCreatedDate DATETIME2(7)',
        @NumberOfItems, @FromCreatedDate;
END

关键优化点 #

1. 消除模糊条件：移除@FromCreatedDate IS NULL OR CreatedDate > @FromCreatedDate这种二选一的模糊逻辑，让SQL Server能针对每种参数场景生成精准的执行计划，触发索引Seek。
2. 参数化执行：保留sp_executesql实现参数化，既避免SQL注入风险，又允许SQL Server缓存不同分支的执行计划，提升后续调用性能。

索引优化建议 #

为彻底避免Seek后的回表操作，建议创建包含过滤条件和返回字段的覆盖索引：

CREATE NONCLUSTERED INDEX IX_MultipartDataFeed_CreatedDate_Active
ON MultipartDataFeed (CreatedDate ASC)
INCLUDE (IsActive, DataKey, FeedData)
WHERE IsActive = 1; -- 过滤索引进一步缩小索引范围

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