ReIndexing and sp_replcmds

[RonFusionHSLLC]

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

[Steve_Nguyen]

the reindex will not affect MS-CDC

[RonFusionHSLLC]

It may not affect MS-CDC but whatever kicks off sp_replcmds is causing huge waits during re-indexing.  I believe its because rebuilding an index causes log entries, which have to be read through.  While nothing down stream seems to be picking up on the index rebuilding, sp_replcmds waits skyrocket.  Below is a 1hr snapshot, sp_replcmds is waiting for near 30mins out of 60mins.  Our reindexing runs for 2-3hours.

The biggest issue is that user waits on the source system increase by 200-300%.  This was not the case prior to replication.

My question is, if we do a sp_cdc_stop_job/sp_cdc_start_job during indexing, will we avoid the waits and still maintain all of the replicated transactions between stop/start...or will this just delay the inevitable and sp_replcmds will run over the logs at the same rate whether we stop/start or not?

[shashi_holla · Accepted solution]

Hi @RonFusionHSLLC 

I'm not a SQL Server expert but stopping the cdc job might actually be helpful. It will avoid the contention during index rebuild and when the job starts again it has to just take care of the final state of the records and less contention. This is just a theory and won't know for sure until we test it out.

Thank you,

[Michael_Litz · Accepted solution]

Hi Ron,

I have seen many customers who will stop the replicate task during a source side maintenance job like re-index. If you stop the task, then run the re index job, then RESUME the task it should read through the changes much quicker then if you leave the task running.

Something to do with the task rereading the logs for each index change (when left running) and when it is Resumed the task will be able to read straight through the changes.

Please give this a try and let me know if it helps out.

Thanks
Michael

[RonFusionHSLLC]

Thanks for the two possible answers.

However there is a difference between them:

Which is better?

Stop cdc with  sp_cdc_stop_job / sp_cdc_start_job 

OR

Stop Qlik Replicate on a stop/Resume schedule

[lyka]

Hi @RonFusionHSLLC 

Here's an article we have in community about rebuilding indexes:

https://community.qlik.com/t5/Official-Support-Articles/SQL-Server-Source-Rebuild-Index-Best-Practic...

hope this helps!

Thanks

Lyka

[RonFusionHSLLC]

Thanks Lyka, I've read that several times, good stuff.

A couple of things, MS-CDC behaves a bit different  on an Azure Sql Managed Instance. 

Backups are out of our control, log management is out of our control...all managed by Microsoft.

We have found that backups and log management do not affect Replicate in any way.  In our on-prem environment, we would stop Replicate or we would end up with deadlocks and failures.

I guess I'll just have to try the stop/start of cdc and compare it to stop/start Replicate.

Ron

[Dana_Baldwin]

Hi @RonFusionHSLLC , just to add a little detail to @Michael_Litz 's post - if the task is left running while an index reorg/rebuild runs, it has to do multiple recursive rescans of the TLOG as the virtual partitions are moved, to find where it left off. If the task is stopped during the index maintenance and resumed afterward, the first pass through the TLOG will be slower than normal but it will only have to scan the full log once instead of recursively, improving overall performance when catching up the task.

Thanks,

Dana

[RonFusionHSLLC]

I see...that makes sense.  I'll be testing starting today, had to hold off for month end processing over the weekend.  I'll start with simply stopping/starting the replication, then test the stop/start cdc stuff.

I'll post findings

ron