Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Add a Custom Index in Master Data Services 2016


By:   |   Read Comments (1)   |   Related Tips: More > Master Data Services

Attend these FREE MSSQLTips webcasts >> click to register


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.

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.

Manage Indexes

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.

Add 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.

Index already exists

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.

Create the index

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.

SQL Agent job

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.

Index list in MDS DB

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:

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.

All indexes on the entity

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.

Index properties

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.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, April 29, 2016 - 5:26:15 PM - Carla Sabotta Back To Top

This a nice walkthrough of the new Custom Index feature for MDS 2016. Thanks Koen for writing the article.

For instructions on installing and configuring MDS 2016, and deploying the updated samples models, see this new "Get Started with Master Data Services (SQL Server 2016)" (https://msdn.microsoft.com/en-us/library/ee633884.aspx) article in Books Online.

 


Learn more about SQL Server tools