Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Recommended MAXDOP for...
Microsoft Dynamics CRM (Archived)

Recommended MAXDOP for SQL instance

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hello,

One of our customer is using SQL Server 2008 R2 in association with Dynamic CRM (DCRM) 2011 - Underlying physical server got 60 CPU and there is no other application running on underlying host.

I had reviewed few expensive queries - most of them are running in parallel, with sometime as many threads as 17-24 threads and CXPACKET pops up as prominent wait type. I have read numerous times that MAXDOP should be set to 8 on OLTP server for optimal performance. Do you still recommend setting MAXDOP to 8 on a host with 64 CPU for enhanced performance?  

Got limited exposure to DCRM and never had opportunity to work on such high end server.

Appreciate your insight and help. Thank you.

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Recommended MAXDOP for SQL instance

    Thank you David for taking time to help !!

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Recommended MAXDOP for SQL instance

    Thank you Andreas

  • Verified answer
    David Jennaway Profile Picture
    David Jennaway 14,063 on at
    RE: Recommended MAXDOP for SQL instance

    It depends how much time you want to spend on this. MAXDOP = 8 is a good start, though you could spend time (quite a lot of it), testing alternate values to see if they are better or worse. However, for the testing you need to have a representative workload to get useful numbers.

    A typical CRM system tends more towards OLTP than OLAP, though in reality it is a combination of both. A complication is that users are encouraged to create ad-hoc queries (via advanced find, quick find, charts etc), so you have little control on what potentially costly queries could be running. That said, most ad-hoc queries are not particularly complex (from a data point of view), and are unlikely to need much parallel processing.

    If you have specific complex queries (most commonly reports), then you may want to spend time trying to tune these, but your effort is generally better spent concentrating on the query and index optimisation, rather than server configuration settings like MAXDOP.

    However, if you do want to change the parallelism settings, I'd also look at 'Cost Threshold for Parallelism' - I'd probably start by increasing that to maybe 50, so only the more complex queries are considered for parallelism

  • Suggested answer
    Andreas Cieslik Profile Picture
    Andreas Cieslik 9,265 on at
    RE: Recommended MAXDOP for SQL instance

    Here is a guide:

    support.microsoft.com/.../recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi

    MAXDOP should stay at 8 is what Microsoft recommends.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans