6

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?

2
  • 2
    It is only 31K records (at the moment) so the benefit of the indexes will not be that great. We're really only interested in relieving the CI scans with indexes and they are less than half of the writes. In my opinion, the penalty for the writes will outweigh the benefit. Sep 4, 2015 at 19:13
  • 2
    You should also include msrepl_transactions for index analysis. Sep 4, 2015 at 19:14

1 Answer 1

4

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.

3
  • 5
    That connect item does not in any way indicate that indexes on these tables are not supported nor does it even say you shouldn't do it. These tables are categorized as user tables, not system tables, and it is supported -- even recommended -- to perform index maintenance or add indexes as needed on these tables. Sep 4, 2015 at 21:37
  • 2
    @RobertLDavis The connect Item that I have cited is only indicating that MS wont create any indexes on that table. I did not say that that connect item indicates that indexes are not supported. I am not sure how do you categorize MS* related as user tables since Microsoft maintains them. Have you came across a scenario where creating Indexes did improve things on distribution database ? If it were msdb related backup tables then additional indexes definitely help. (1/2)
    – Kin Shah
    Sep 4, 2015 at 23:30
  • 2
    As per my answer, there are things that you can do to tune things on distribution database, but I have not encountered any scenarios where creating missing indexes helped in distribution database. I know that you are an MCM, so would like to learn from your experience. Also, I do often read your blog posts (SQLSoldier) to get some knowledge from them. Also, I am not denying that those indexes are not useful, but making a point that dont just blindly create them unless you have tested and proven that they will benefit your workload.
    – Kin Shah
    Sep 4, 2015 at 23:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.