MDS Hiccups: Common Issues When Tweaking Entities, Users, and Groups in SQL Server

Table of Contents

Taming Your MDS Hiccups: A Casual Guide to Fixing Performance Issues¶

Hey everyone! Ever feel like your Master Data Services (MDS) is having a bit of a meltdown when you’re tweaking entities, attributes, users, or groups? You’re not alone! It’s like trying to change a tire on a moving car – tricky and sometimes painful. This guide is here to help you diagnose and fix those pesky performance problems, so you can get back to smoothly managing your master data.

image just illustration

What’s the Deal with These Performance Issues?

Let’s be real, nobody likes staring at a loading screen for ages or dealing with memory hogs. These are the typical symptoms you might encounter when messing with your MDS setup:

• High Memory Usage: Your system starts chugging like it’s trying to run Crysis on a potato.
• Long Load Times: Waiting for pages to load feels like watching paint dry.
• Slow Response Times: Clicking a button and waiting an eternity for a reaction is just frustrating.

These issues can pop up when you’re adding, removing, creating, or modifying entities, attributes, users, or groups.

Why Is This Happening?

Okay, so MDS got a pretty cool upgrade that made GET operations (like viewing data) way faster. It’s like getting a turbocharger for your data retrieval. But… (there’s always a but, isn’t there?) this improvement came with a slight trade-off. The performance of modifying things like entities, attributes, users, and groups took a bit of a hit. It’s like souping up your engine for speed but sacrificing some handling.

Here’s the breakdown:

• The Good Stuff (Performance Improvements):

  • Blazing Fast Loading: Pages that require permission checks (like the explore page, model page, entity list page, and attribute list page) load much quicker.
  • No More Timeouts: Say goodbye to those annoying timeout exceptions on the model permissions setting page.
  • Speedier Business Rules: The add/edit condition page on the Business Rules page loads in a snap.

• The Not-So-Good Stuff (Side Effects):

  • Slower Modifications: Creating or modifying entities and attributes can feel a bit sluggish.
  • User Management Slowdown: Adding users or groups isn’t as zippy as it used to be.

The Fix: Flipping the Switch

Luckily, there’s a simple solution to this performance conundrum: the PerformanceImprovementEnable system setting. It’s like a magic switch that lets you prioritize either fast GET operations or fast modifications.

• Need Speed for Viewing Data? (Enable the Setting)

If you primarily need to quickly view data and permissions, enable the performance setting by running these commands in SQL Server Management Studio (SSMS):

UPDATE mdm.tblSystemSetting SET SettingValue = 1 WHERE SettingName = 'PerformanceImprovementEnable';
EXEC [mdm].[udpPerformanceToggleSwitch];
GO

This will make loading large user security-related data significantly faster.

• Need Speed for Modifications? (Disable the Setting)

If you’re doing a lot of creating and modifying entities, attributes, users, or groups, it’s better to disable the performance setting. Run these commands in SSMS:

UPDATE mdm.tblSystemSetting SET SettingValue = 0 WHERE SettingName = 'PerformanceImprovementEnable';
EXEC [mdm].[udpPerformanceToggleSwitch];
GO

This will make creating and modifying models, entities, and attributes a much smoother experience.

Choosing the Right Setting

The key is to find the balance that works best for your specific needs. If you’re mostly viewing data, keep the setting enabled. If you’re heavily involved in model creation and modification, disable it. It’s all about what you need to do most efficiently. Think of it like choosing between a sports car (fast viewing) and a pickup truck (heavy lifting modifications) – the right choice depends on the job.

What We’ve Learned

We’ve covered the symptoms of MDS performance issues, the root cause of these hiccups, and, most importantly, how to fix them by toggling the PerformanceImprovementEnable setting.

Now, it’s your turn! Experiment with the setting, see what works best for your workflow, and conquer those performance woes. Don’t forget to share your experiences and thoughts in the comments below! We’d love to hear your tips and tricks for optimizing MDS performance. What other challenges have you faced, and how did you overcome them? Let’s learn from each other and make MDS management as smooth as butter!