I have a problem that every few weeks, the get_net_changes function does not pick up some change data capture rows that are within the lsn range. Then I have variances from the source system and either need to do a full load or update the data in the source so that it is captured again.

I'm wondering if it is perhaps because the functions use "with (nolock)". Is there a reason this hint is used? Would we get deadlocks without it? Couldn't this be a problem because with the hint there is the potential for missed rows or dirty reads.

But I'm hesitant to just go through the functions and remove the hint as I don't want to mess them up.