Slow Entity Creation and Missing Attributes in Master Data Services 2016 – Help Needed!

Question

Hi everyone,

I'm encountering a strange issue with Microsoft Master Data Services 2016 that started today. Everything usually works perfectly, but now when I try to create a new entity in my model, the process takes 2-3 minutes. When I check the Activity Monitor in SQL Server 2016, I see that it’s stuck on a WAITFOR command, and specifically, it's executing mdm.udpPerformanceQueueExecuted, where it hangs for a long time.

Eventually, the entity gets created, but when I click on the attributes, I find that none have been created—neither the "Code" nor the "Name" attributes that are usually there. If I then connect to the entity via Excel, I get a "divide by zero" error along with several other errors, making it impossible to work with the entity.

I found a similar issue discussed in this link, but the "repair database" option is grayed out and unavailable for us, so it seems the database is okay. I also ran a Database Compare between our test and production servers but couldn't find anything unusual. The problem only occurs on production.

I’ve tried the following steps as suggested in this article:

ALTER DATABASE DWH_NL_MDS SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE DWH_NL_MDS SET ENABLE_BROKER WITH NO_WAIT; ALTER DATABASE DWH_NL_MDS SET MULTI_USER WITH ROLLBACK IMMEDIATE;

However, there's still one row in the [mdm].[microsoft/mdm/queue/stagingbatch] table that won’t clear, even after following these steps: (don't know if this is the cause of the problem...)

SELECT * FROM [mdm].[microsoft/mdm/queue/stagingbatch];

We rely heavily on MDS and need to add some new tables, which isn’t working due to this issue. I've attached some screenshots showing the problem.

Does anyone have any ideas or a golden tip on how to resolve this? Your help would be greatly appreciated!

Thanks in advance!

Answer 1

Update:

I've executed the following code:

EXEC DWH_NL_MDS.mdm.udpPerformanceQueueExecuted;1

This is the code where It keeps hanging and seems to be stuck in some kind of infinite loop, as I keep seeing the same output repeatedly. This makes me suspect that there might be some bad data in one of the tables that it's getting stuck on.

When I look into the stored procedure, I see that this piece of code is being executed:

SELECT status, conversation_group_id, conversation_handle, service_name FROM mdm.[microsoft/mdm/queue/performance];

Does anyone have any idea how to resolve this issue? Any help would be greatly appreciated!