Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We need to re-index our source indexes almost every day.
Our database is an Azure Sql Managed Instance, we are using MS-CDC.
We have observed a huge increase in waits for sp_replcmds during this process. Understandably, as an index rebuild shows up as inserts into the table.
Are there any mechanisms to allow for ignoring index rebuild transactions, and calming down sp_replcmds waits?
Is it reasonable to use sys.sp_cdc_start_job and sys.sp_cdc_stop_job during re-indexing. We have yet to used these commands, so not sure what happens if cdc is stopped and then started...
It would seem to me that stopping, indexing and starting would minimize the waits as both processes are not competing. What happens when cdc_start_job is run, won't that cause the same waits as all that re-index log data has to be processed?
Any advise would be appreciated
ron
Here are the graphs wherein Replicate was stopped, re-indexing ran and Replicate was resumed. It still looks like sp_replcmds/repldone have huge waits. While this made things a bit better, I don't think it had any effect per-se on the performance overall during re-indexing. Replicate was started at 5:30, and had hardly any effect on performance while it was catching up. Little hard to see - the largest bar is replcmds, the next bar is repldone
Next, I will try to stop cdc with sp_cdc_stop_job / sp_cdc_start_job during the same period.