Improving Master Data Services Performance by Indexing Non-Domain Attributes

Adding an index on an MDS member table could significantly improve the performance of entity member lookup by non-domain attributes with large numbers of entity members.

This index would have to be non-unique and non-clustered. You cannot add a clustered index because only one is allowed per table, and the member tables already have a clustered index. You cannot add a unique index because that could cause constraint violations. (If you want to enforce uniqueness, then you should do so with a business rule instead.)

You would need to add the index manually. There is currently no way to add such an index using the Master Data Manager Web application or the MDS APIs. If you get a warning from the query optimizer, and you are willing to endure slower write performance in exchange for faster read performance, then you could follow the recommendation of the query optimizer and add the suggested index, as long as it is non-clustered and non-unique. Note that the index may not be retained after a product upgrade. After an upgrade, you will need to check that it still exists.

The following is an example of an index that you could use to improve performance:

USE [MDS_MA] 
GO 
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] 
ON [mdm].[tbl_2_6_EN] ([Version_ID],[Status_ID],[uda_6_115]) 
  
GO