question

sreejitg-8603 avatar image
0 Votes"
sreejitg-8603 asked SeeyaXi-msft commented

Sql Server distribution database non-clustered index

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-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @sreejitg-8603,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it is not help, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @sreejitg-8603,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.