I'm upgrading from SQL Server 2008 R2 to SQL Server 2014 (SP3-CU4-GDR) (KB4583462) - 12.0.6433.1.

Compatibility mode is set to 120. Trace Flag 9481 is enabled. The problem still is present in lower compatibly modes, and without flag 9481.

cdc.fn_cdc_get_net_changes_<capture_instance> has changed per KB3030352. There is now a really nasty join involving $__command_id which was not there before.

Unfortunately the change leads to bad performance when there is a lot of data to be fetched by the function. This is inconsistent - sometimes a few million rows are fine, some times 30k rows will not return after an hour: performance was always great on SQL Server 2008 R2.

I've never hit the bug mentioned in KB3030352 in over ten years with billions of rows replicated across hundreds of tables (that includes SQL Server 2008 before we moved to R2). I suspect the bug can only occur if you are not using the primary key as the CDC unique key. I always use the primary key for CDC tracking, hence it has always been impossible for cdc.fn_cdc_get_net_changes_<capture_instance> to return two rows for one primary key value, as described in KB3030352.

  • 2
    Have you got query plans you can share via brentozar.com/pastetheplan Oct 9 at 19:29
  • I think I found the issue. _CT tables created on the old database do not have $__commandid in the clustered index. The upgrade process adds the new column to the _CT tables and the the non clustered index, but not to the clustered index. Will fix by disabling/enabling CDC on each table, test this week and report back. Oct 10 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.