locked
MDS Performance issue RRS feed

  • 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
    Thursday, June 2, 2011 11:08 AM

Answers

  • 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

     

    Thursday, June 9, 2011 7:07 PM
  • 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.
    Thursday, June 23, 2011 12:59 PM
  • 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.

    Thursday, June 2, 2011 11:26 AM
  • 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".
    Tuesday, June 7, 2011 1:57 PM
  • 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.
    Wednesday, June 8, 2011 10:35 AM

All replies

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

    Thursday, June 2, 2011 11:26 AM
  • 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
    Thursday, June 2, 2011 1:13 PM
  • Have anyone facing performance issues?  

    Any help is appreciated.


    Lolita Broka
    Tuesday, June 7, 2011 12:51 PM
  • 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".
    Tuesday, June 7, 2011 1:57 PM
  • 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.
    Wednesday, June 8, 2011 10:35 AM
  • 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

     

    Thursday, June 9, 2011 7:07 PM
  • 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.
    Thursday, June 23, 2011 12:59 PM