SQL Server Master Data Services Performance Tuning Guidelines

SQL Server Master Data Services Performance Tuning Guidelines

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.

You have two deployment options for Master Data Services:

• Single Server Deployment with IIS and SQL Server on the same box.
• Two Tier Architecture with IIS and SQL Server on different servers.

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:

• MDS is designed for slow changing fairly static data. For high transactional data, MDS should not be considered.
• Anything more than 50k distinct changes will have performance degradation.
• Large changes should be performed in batches rather than individually.
• Proof of concept is important for large deployments.

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:

Medium Capacity Models:

• Entities with >500k members
• No. of Attributes in Entities >100
• Domain based attributes >20
• Up-to 15 business rules
• >=5 user concurrency
• Hardware with 12-16 GB memory, at-least Dual CPU and High performance storage is recommended.

Large Capacity Models

• Entities with >10000000 (10 million) members
• Attribute members>1000000 (1 million) with 100 attributes each
• Domain based attributes >30
• Up-to or more than 15 business rules
• >=5 user concurrency
• Hardware with at least 24 GB memory, Dual Socket Quad CPU and High performance storage is recommended.

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.

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

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.

Please refer to the Official documentation from Microsoft for your Reference:

• Master Data Official Blog
• Master Data Services Capacity Guidelines