QUick one... SSIS CDC Source component is producing a suboptimal plan.
The SSIS CDC Source component appears to be calling the fn_cdc_get_net_changes_<capture_instance> function using sp_executesql, but passes the from_lsn and to_lsn as an nvarchar(22). This results in a suboptimal plan and begins causing time-outs for what looks a trivial task. This is obviously only an issue with a high volume of changers in the capture processing range.
The same statement but with binary(10) parameters produces a much better plan and completes in a more acceptable time frame given the workload.
I suspect fn_cdc_get_all_changes_<capture_instance> also has issues, but given that the work involved is lower, I not had issues with this.
This was identified on SQL 2012 SP2 on VMWare. Our only workaround has been to massively extended the timeout setting.
No comments:
Post a Comment