MDS Performance issue

Question

Experiencing very slow MDS performance in UI: data brosing, modification/new, filtering, hierarchy management.

Test model is simple, contains ~10000 members. No validation rules or workflows exist at the moment. Model have only 1 initial version for now.

Verion: 10.50.1777.0 (CU7). UI language: english.

OS: Windows Server 2008 R2 Standard.

 

---

Lolita Broka

Answer 1

Hi,

The UI is slow initially, but you should find that the pages are quicker once they're cached. But even after caching it is still a bit slow in my experience.

The following threads may help you:

http://social.msdn.microsoft.com/Forums/ar/sqlmds/thread/7cde16d0-23f2-4780-8060-38ace18f987c

http://social.msdn.microsoft.com/Forums/en/sqlmds/thread/5dd5edf4-44b5-41f9-b136-91619b361115

Best regards

Jeremy

---

http://blogs.adatis.co.uk/blogs/jeremykashel/default.aspx

If a post answers your question, please click the Mark As Answer button.

Answer 2

Thank you for responce.

I've seen these topics before. Still have performance problem.

• UI is set to 'English' (strange performance tuning in MDS.. ) ; 
• Cache helps a little, but it is slow anyway;
• Server: CPU 8x2.66Ghz, 8Gb RAM; Data and App on the same server;

 

---

Lolita Broka

Answer 3

Have anyone facing performance issues?  

Any help is appreciated.

---

Lolita Broka

Answer 4

Hi Lolita

Yes, and I can't find out why it is happening sometimes , and sometimes it is fast

I've separated App Tier and Data Tier but I can see the same issue, but on some other servers, it is very fast, with almost the same configuration.

Regards,

---

---

Xavier Averbouch
Microsoft Community Contributor
Avanade , FRANCE
If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".

Answer 5

Hi Lolita,

One other thing that you may want to try is to look at Resouce Monitor in Windows. When I did this, I noticed that actions in the MDS front end cause CPU and Disk resources to be hit more than memory.

That's on my laptop, which is 8GB RAM, 1 proc, 4 cores. Data and App on the same machine. Although CPU and disk are hit, CPU certinaly gets nowhere near maximum.

I did notice that w3wp.exe (IIS worker process) uses a fair amount of CPU, and also that files in C:\Program Files\Microsoft SQL Server\Master Data Services\WebApplication get used by w3wp.exe when you go to various pages in the front end. Sqlserver.exe also uses CPU at different points to w3wp.exe.

So using fast disks, sperating IIS and SQL onto seperate servers, making sure that MDS SQL log and data files are on seperate disks also are things that may help in your environment. Using Resource Monitor should help you narrow down which resources are getting hit, so I'd recommend starting there.

Hope that helps

Jeremy

---

http://blogs.adatis.co.uk/blogs/jeremykashel/default.aspx If a post answers your question, please click the Mark As Answer button.

Answer 6

We were running into a performance issue just like that.  Any time we were doing anything in the UI it was slow.  After some investigation into SQL Server profiler we discovered that there was a query that was taking a LONG time to execute.   There was over 14 million records in the sys.conversation_endpoints table which slowed down many queries.

We used the following script to clean the conversation end points, add the 4 normal messages, and then update statistics.

 

-- clean up service broker messages

DECLARE

@Handle

UNIQUEIDENTIFIER

WHILE

(SELECT COUNT(1) FROM sys.conversation_endpoints)<>0

BEGIN

 

SELECT TOP(1) @Handle = Conversation_handle FROM sys.conversation_endpoints 

END

CONVERSATION @Handle WITH CLEANUP

END

GO

--Start the member security Queue

DECLARE

@handle UNIQUEIDENTIFIER

;

BEGIN

DIALOG CONVERSATION @handle

 

FROM SERVICE [microsoft/mdm/service/securitymembertimer]

 

TO SERVICE N'microsoft/mdm/service/system' 

 

--WITH RELATED_CONVERSATION_GROUP=0x1; 

 

WITH ENCRYPTION=OFF --is by default

BEGIN

CONVERSATION TIMER (@handle) TIMEOUT = 30

;

GO

 

--Start the StagingBatch Queue

DECLARE

@handle UNIQUEIDENTIFIER

;

BEGIN

DIALOG CONVERSATION @handle

 

FROM SERVICE [microsoft/mdm/service/stagingbatch]

 

TO SERVICE N'microsoft/mdm/service/system'

 

WITH ENCRYPTION = OFF;

BEGIN

CONVERSATION TIMER (@handle) TIMEOUT = 30

;

GO

--Start the Notification Queue

DECLARE

@handle UNIQUEIDENTIFIER

;

BEGIN

DIALOG CONVERSATION @handle

 

FROM SERVICE [microsoft/mdm/service/notification]

 

TO SERVICE N'microsoft/mdm/service/system'

 

WITH ENCRYPTION = OFF;

BEGIN

CONVERSATION TIMER (@handle) TIMEOUT = 30

;

GO

--Start the External Action Queue

DECLARE

@handle UNIQUEIDENTIFIER

;

BEGIN

DIALOG CONVERSATION @handle

 

FROM SERVICE [microsoft/mdm/service/externalaction]

 

TO SERVICE N'microsoft/mdm/service/system'

 

WITH ENCRYPTION = OFF;

BEGIN

CONVERSATION TIMER (@handle) TIMEOUT = 30

;

GO

-- refresh SQL Server query statistics after the cleanup

EXEC

sp_updatestats

 

Answer 7

Hi Lolita,

Just to add to this: I've just got round to installing IE9 - and that seems to have improved the performance for me. This is only my perception, I've not done any scientific testing to prove this, but it does fit with several of my colleagues telling me that IE9 is faster for some other applications.

Worth you trying it if you haven't already

HTH

Jeremy

---

http://blogs.adatis.co.uk/blogs/jeremykashel/default.aspx If a post answers your question, please click the Mark As Answer button.