Sql Server distribution database non-clustered index

Question

Hi,

Does Microsoft recommend/support to create non-clustered index in distribution database table Msrepl_commands? Our informatica CDC queries is running longer and execution plan suggest 99% impact to create missing non-clustered index. Once we created the non-clustered index the perf issue stand fixed but double checking if Microsoft support creating indexes in system databases?

Thanks,
-SreejitG

Answer 1

MS does not support user changes to MS supplied databases. They can be changed during patching/upgrade and cause a conflict.

However, there is nothing to stop you from doing it, with the understanding it might break something later.

Answer 2

Hi @sreejitg ,

No, this is not recommended/supported. But this does not mean that you cannot create those indexes.
Microsoft does not support making any changes to the system tables (replication, logshipping).
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.
Finally, it is important to note that do not just blindly create them unless you have tested and proven that they will benefit your workload.

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.