question

ramana avatar image
ramana asked

Replication performance & Indexes rebuild

Hi SQL Experts,

I have some questions on Replication, which I couldn't find the answers.

How to find why the publication is slow in performance after getting alerts. Is rebuilding/reorg indexes on subscriber will help to improve the performance? How the high row count decrease the performance?


replication
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
anthony.green avatar image
anthony.green answered

Replication is just a series of procedures under the hood, so you troubleshoot it the same as you would any other procedure normally.

Look at the plans and see what’s going wrong.

To many indexes on the subscribers can also hurt performance.


There are published docs on how to get the best performance from replication like the one below for TX repl.

https://docs.microsoft.com/en-us/sql/relational-databases/replication/administration/enhance-transactional-replication-performance?view=sql-server-ver15


High row counts don’t affect repl that much, keep the primary key as narrow as you can and you won’t have a problem due to the unique row property of the PK so the optimiser can get to the row quickly.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.