2

Good morning team,

I’m really struggling to improve the performance of this function: sys.fn_cdc_map_time_to_lsn. It is a system function which is used by CDC, you can read a full description of what it does here: https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-cdc-map-time-to-lsn-transact-sql?view=sql-server-2016

I can see that under the hood when it is called, it is executing something like

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

The plan it is using is doing a full index scan on the system created clustered PK index, which is wildly nonoptimal and takes about 12s on avg.

I created a NC index to help

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

When I tested the index, the query was finishing 32x faster, completing in 0.5s approx instead of 12s.

Here is how I tested

a) With Index forced - completes in about 0.5s

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)

enter image description here

b) Without Index forced - chooses to scan Clustered PK and completes in about 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)

enter image description here Even after recompiling, updating stats, dropping stored plans etc both my test script B above and the function sys.fn_cdc_map_time_to_lsn refuse to choose the faster NC index.

I’d be tempted to edit the function to force the NC index, but I can’t edit a system function.

I could create a user defined function with the index hint which did the same thing, and tell the App team to call that instead- but that seems enormously hacky and risky when it comes to updating or upgrading so I’m not very keen.

Does anyone have any good ideas or insights for what I could try to fix this?

Here are the anonymized plans for the two test queries. You can clearly see which one I forced the index on :)

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

5
  • Please provide the execution plans for both the slow and fast version. Even 0.5s sounds pretty slow for the query behind that function, when indexed properly. Perhaps a columnstore index could help too.
    – J.D.
    Jan 12, 2023 at 20:57
  • Apologies, I have added screenshots of the execution plans now. I'm kind of reluctant to paste the actual execution plans because it includes more information about where I work and what our servers are like than I'm comfortable putting out publicly, so I hope the screenshots are helpful enough.
    – Zoe
    Jan 12, 2023 at 21:17
  • Unfortunately screenshots show little information compared to the actual plan. The execution plans shouldn't contain much about the server itself (it's mostly just names of the objects - tables and indexes at play), especially for one of Microsoft's system functions. But you can anonymize the plans if you're concerned.
    – J.D.
    Jan 12, 2023 at 22:38
  • 1
    Alright, I'll do that now :) Please hold caller...
    – Zoe
    Jan 12, 2023 at 23:04
  • 1
    Done! Thanks for your help.
    – Zoe
    Jan 12, 2023 at 23:15

1 Answer 1

2

I ended up creating a user defined function which was identical to the original system defined function, except for it included an index hint for the index above. Then, I asked the App Developers to call that function instead of the system function. This worked well for our purposes.

Other ideas I explored included using query hints to try get the function to use my index (no luck), using plan guides (no luck) and playing around with different indexes, stats updates etc (no luck).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.