By: Koen Verbeeck | Comments (1) | Related: > Master Data Services
Problem
In the new release of Master Data Services (MDS) in SQL Server 2016, a couple of new features have been added in order to improve performance. In this tip, we'll introduce the capability to create a custom index on an entity.
Solution
SQL Server 2016 Preview
At the time of writing, SQL Server 2016 is still in preview (currently Release Candidate 3 (RC3) has been released). This means functionality or features of Master Data Services might change, disappear or be added in the final release.
Test Set-up
This tip uses the standard sample models that are installed alongside MDS on the server. The sample models have been updated in SQL Server 2016 RC1 to showcase some of the new features. The MSDN blog post Deploying MDS Samples in SQL Server 2012 explains the installation process of the samples. The blog post is for SQL Server 2012, but the method is still the same in SQL Server 2016.
Add an index to an entity in Master Data Services
One of the new features in MDS 2016 is the possibility to add a custom index to an entity in order to improve query performance on one or more attributes of the entity. The first step is to go to System Administration. There you can find the index maintenance under Manage | Indexes.
In the Manage Indexes screen, you can select the Model and an entity. If there are any indexes for the entity, they will be listed below.
Let's try to add an index to the Customer entity of the Customer model. To create an index, you need to give it a name and choose the attributes that will be part of the index. You can either choose one attribute, or multiple if you want to create a composite index.
If you select the Is Unique checkbox, the attribute cannot contain duplicate values, or the combination of the values of the different attributes needs to be unique. There is no further indexing options, such as the ability to make the index clustered for example. If the index already exists, an error will be shown.
This is perfectly possible, since MDS already creates quite some indexes on an entity, such as on the code attribute and on domain based attributes. The problem is that there is no easy way to find out which indexes already exist, which makes it a bit of trial and error in order to create an index. Once you add an attribute that doesn't exist yet in other indexes, you can create the index. Let's add city to the index and create it.
Be aware that you cannot change the column order in the MDS designer. Column order is very important in indexes, as explained in the tip SQL Server Index Column Order - Does it Matter. If you have an incorrect column order, you need to remove columns and add them back in order to get the correct order. There are some other rules as well:
- You cannot change the type of an attribute that's included in a custom index
- If you want to delete an attribute that's part of a custom index:
- if the attribute is the only attribute, the index and the attribute will be deleted
- if the index is a composite index, the attribute needs to be removed from the index first before you can delete the attribute
There is no maintenance required on the indexes. At the installation of MDS, a SQL Server Agent Job is created that takes care of the index maintenance.
By default, the job is scheduled to run once a week. If you're unhappy with this schedule, you can easily modify it in the job itself.
Taking a look behind the scenes
The Manage Indexes screen in the MDS only shows you the custom indexes created for a selected model and entity. To find out which custom indexes have been created on the various models, you can query the table mdm.tblIndex in the MDS database. Be aware that this is not officially supported, as the MDS data model can change without notice.
In the query results, we can see the index we've just created. Having a good naming convention for the indexes will be of great assistance in managing the indexes. You can see the index was created on the entity with ID 18, which is the Customer entity (this can be verified in the mdm.tblEntity table). It's possible that the entities have different IDs on your system. The SysIndex_ID is the system ID of the index in SQL Server. You can use it to find the actual index:
As you can see below, the physical index doesn't have the same name as the custom index in MDS. It follows the naming convention: ixud + "table name of the entity" + Version_ID (which is included in almost every index MDS creates) + "attribute IDs of the index".
Here, 469 is the country attribute and 480 the city attribute. When we take a look at the index on the table, we can see that quite a lot have been defined. There's also one on the attribute 469 - the country attribute as it is a domain based attribute - so that's why we couldn't create it earlier.
When we take a look at the index itself, we can see that Status_ID column has also been added to this index as well.
Conclusion
Master Data Services 2016 allows you to create a custom index on one or more attributes of an entity. This is a new performance tuning trick in the MDS toolbox. However, it's hard to see which indexes have already been added by MDS on a given entity, without diving into the MDS model in the MDS database.
Next Steps
- Try it out yourself! Install the sample models and start adding some indexes!
- You can find more Master Data Services tips in this overview.
- For more SQL Server 2016 tips, you can use this overview.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
