如何强制系统函数在Server中使用索引？

Question

早安小队，

我真的在努力提高这个函数的性能: sys.fn_cdc_map_time_to_lsn。它是CDC使用的一个系统功能，您可以阅读它在这里所做的全部描述：https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-cdc-map-time-to-lsn-transact-sql?view=sql-server-2016。

我可以看到，在引擎盖下，当它被调用时，它正在执行类似于

select @lsn = min(start_lsn)
        from [cdc].[lsn_time_mapping]
        where tran_end_time >= @tracking_time

它正在使用的计划是对创建的聚集PK索引系统进行一次完整的索引扫描，这是非常不理想的，在avg上大约需要12s。

我创建了一个NC索引来帮助

CREATE INDEX  NC_lsn_time_mapping__tran_end_time_start_lsn  ON [cdc].[lsn_time_mapping] (tran_end_time, start_lsn)
with (online=on);

当我测试索引时，查询的完成速度快了32倍，完成速度为0.5s左右，而不是12s。

以下是我如何测试

( a)索引必须在0.5秒内完成

declare @tracking_time datetime
set @tracking_time = '2023-01-12 8:01:30.430';
declare @lsn binary(10);

select @lsn = min(start_lsn)
        from [cdc].[lsn_time_mapping]
        WITH(INDEX([NC_lsn_time_mapping__tran_end_time_start_lsn]))
        where tran_end_time >= @tracking_time

option (recompile)

( b)没有索引强制-选择扫描集群PK并在大约12s内完成

declare @tracking_time datetime
set @tracking_time = '2023-01-12 8:01:30.430';
declare @lsn binary(10);

select @lsn = min(start_lsn)
        from [cdc].[lsn_time_mapping]
        where tran_end_time >= @tracking_time

option (recompile)

即使在重新编译、更新统计数据、删除存储计划等之后，我的测试脚本B和函数sys.fn_cdc_map_time_to_lsn都拒绝选择更快的NC索引。

我很想编辑这个函数来强制NC索引，但是我不能编辑一个系统函数。

我可以用索引提示创建一个用户定义的函数，它可以做同样的事情，并告诉instead调用它--但在更新或升级时，这似乎是非常麻烦和风险很大的，所以我并不热衷。

有人有什么好的想法或见解，我可以尝试解决这个问题吗？

以下是两个测试查询的匿名计划。您可以清楚地看到我强制索引的是哪一个:)

( a) https://www.brentozar.com/pastetheplan/?id=rkjENzR5j b) https://www.brentozar.com/pastetheplan/?id=S1hZ4zAcj

Answer 1

最后，我创建了一个与原始系统定义函数相同的用户定义函数，但它包含了上面索引的索引提示。然后，我要求应用程序开发人员调用该函数，而不是系统功能。这对我们的目的是很好的。

我探索过的其他想法包括使用查询提示来尝试获取函数来使用我的索引(没有运气)、使用计划指南(没有运气)和使用不同的索引、统计数据更新等等(没有运气)。