Master Data Services is a MDM (Master Data Management) system from Microsoft and ships as a part of the SQL Server distribution. Most of the computational tasks are done on the SQL Server side and hence SQL Server performance tuning is important for an optimal MDS deployment. In this post I am going to talk about the SQL Server Master Data Services performance tuning guidelines.
Note: If you buy something from our links, we might earn a commission. See our disclosure statement.
Any kind of Production deployment should consider a 2-Tiered approach. The IIS Tier is fairly lightweight and can easily service hundreds of users with 4 Gb of RAM and a 2 Core processor running on a Virtual Machine (VM). The SQL Server on the other hand needs to be capable enough to handle most of the transactions and processing for MDS. Before even considering MDS, you need to keep in mind the following points:
Microsoft has different capacity models based on the number of members in an entity and number of attributes in that entity. Let’s summarize them below:
For capacities of more than what is mentioned above, a complete performance testing and proof of concept is highly recommended.
Microsoft also recommends creating index’s on the member tables of non domain attributes, especially which has large number of members.
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [mdm].[tbl_2_6_EN] ([Version_ID],[Status_ID],[uda_6_115])
The above step needs to be performed manually.
A good number of performance improvements has been implemented in MDS for SQL Server 2016 which I will talk about in a different post.
Comments are closed.