Recommended MAXDOP for SQL instance

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (4)

Answers (1)

Suggested answer

Andreas Cieslik 9,267 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

David Jennaway 14,065 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Recommended MAXDOP for SQL instance

Thank you Andreas

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Recommended MAXDOP for SQL instance

Thank you David for taking time to help !!

Was this reply helpful? Yes No