SQL Server – Missing Indexes on Distributor Database

replicationsql servertransactional-replication

Looking for missing indexes on my distributor databases, I surprisingly find too many.

the tables that need to be indexed are generally 3:

MSrepl_commands

MSlogreader_history

MSdistribution_history

Here is the picture of the missing indexes

enter image description here

Now concentrating on TABLE MSdistribution_history

It all seems to boil down to these 3 indexes:

USE distribution_BOCSS;
CREATE NONCLUSTERED INDEX 
IDX_agent_id_time_xact_seqnorunstatus_INC_delivery_latency 
ON [distribution_BOCSS].[dbo].[MSdistribution_history] 
 ([agent_id], [time], [xact_seqno], [runstatus]) 
 INCLUDE (delivery_latency,start_time,timestamp) WITH (ONLINE = ON)
        
        
USE distribution_BOCSS; 
CREATE NONCLUSTERED INDEX IDX_runstatus_INC_agent_id_delivery_latency 
ON [distribution_BOCSS].[dbo].[MSdistribution_history] 
([runstatus]) 
INCLUDE (agent_id,delivery_latency,time,xact_seqno,timestamp) 
WITH (ONLINE = ON)
    
    
    USE distribution_BOCSS; 
    CREATE NONCLUSTERED INDEX IDX_agent_idrunstatus_INC_time_xact_seqno_delivery_latency 
    ON [distribution_BOCSS].[dbo].[MSdistribution_history] 
([agent_id], [runstatus]) 
    INCLUDE (time,xact_seqno,delivery_latency,timestamp,start_time)
     WITH (ONLINE = ON)

However, as I would imagine, the amount of updates on the current existing clustered index is very high (not sure since when it has been adding up to these stats, possibly since the last reboot)
as you can see on the picture below:

enter image description here

The writes are too high, it is a production system, I would like to have more support regarding this. Would I really benefit by creating these indexes?

Is there any documentation that supports creating indexes on the distributor databases of transactional replication?

Why Microsoft has not supplied these databases with the proper indexes on them?

Best Answer

Replication normally works and I have not encountered any issues where I have to create an additional index on the distribution database. (In my company we use replication a lot for doing data movement from one region to another)

If you are not having problems with Replication, then avoid creating those indexes. Index creation is not FREE, since SQL Server has to maintain any DDL changes that means you will have to do a proper maintenance for those indexes.

There is tuning that you can do. For e.g. if immediate sync is set to TRUE for your Publication then that will cause the transactions to be held in MSRepl_commands rather than deleting them when distributed. So setting immediate sync to FALSE will help the distribution clean up job do clean up much faster.

Is there any documentation that supports creating indexes on the distributor databases of transactional replication?

Microsoft does not support making any changes to the system tables (replication, logshipping) except you are told by CSS. This does not mean that you cannot create those indexes, it just means that if you open a case with Microsoft they wont support unless you remove the indexes that you have created by yourself.

Check this connect as won't fix - Add index on [distribution].[dbo].[MSrepl_commands]

So my recommendation would be, baseline your server instance. Get data for a longer duration and if you are facing problems then address them by creating appropriate indexes. Test, test and test all your changes first and always be on the supported side when you work on a vendor software. You can file a connect item and if Microsoft addresses it, then it will be included as hotfix or in future versions.

Related Question