How to improve compare repair performance? (4217949)

Slow running compare

Running compare repair config with combination of large and small tables

Resolution 1

Set the SP_DEQ_THREADS 1 before comparing big tables . Set SP_DEQ_THREADS 2+ before comparing small tables.

Example with running compare config for large tables:

sp_ctrl> set param SP_DEQ_THREADS 1
sp_ctrl> compare config {file_name for large tables}
[repair]


Example with running compare config for small tables:

sp_ctrl> set param SP_DEQ_THREADS 2
sp_ctrl> compare config {file_name for large tables}
[repair]


For large tables, you can also use repair1 option instead of repair option, this should help speed up compare/repair.
sp_ctrl> compare config {file_name for large tables} [repair1]

Resolution 2

If there are more than 50 compare *desvr* logs on the source, remove all compare logs before running another compare job. Too many compare logs will slow compare performance.

sp_ctrl > remove log compare all

If too many out of sync are involved, the “nosqllog” option of compare/repair can be used to improve the performance of compare/repair. See the Knowledgebase article 228415 for details.

SP_DEQ_THREADS param can be adjusted depending whether table is large or small

This parameter controls the number of processing threads used by the compare config

command on the source system. Each thread creates a sp_declt process on the target

machine.