Sql Server distribution database non-clustered index

sreejitg 41 Reputation points
2021-05-24T17:22:17.86+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,242 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Tom Phillips 17,706 Reputation points
    2021-05-24T20:57:09.893+00:00

    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.

    0 comments No comments

  2. Seeya Xi-MSFT 16,411 Reputation points
    2021-05-25T06:36:22.097+00:00

    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.

    0 comments No comments