PLaying with Max Degree of Parallelism Option in SQL Server 2012 and higer

[former_member206857]

So after reading that great article, I was eager to try this out.

I so happen to have a physical intel server 16 CPU's.

So I ran a test of a DBCHECK at parallelism 1. Took 4:20 hours.

Changed parallelism to 2, ran DBCHECK again, took 2:22 Hours.

Aprx 45% increase in time.

So Then I re-ran the same test again and the times came in within seconds of those above.

Now, I'm looking for another test, something DB extensive and that is relatively the same test each time.

I'm wondering if an SGEN would work?

Anyone have a suggestion of an SAP job I would try to benchmark and play with this?

[former_member211576]

Hi Joshua,

I don't think you can speed up SGEN because it is already run in parallel by all available application server and work process.

  I also believe most standard SAP transactions can't be boosted because most ERP/OLTP queries are short and quick. If you have to test, you can find your top expensive SQL statements(custom codes) in DBACockpit -> Performance -> SQL statements. Please note that you have to drop procedure cache first in order to create a new execution plan for parallel processing.

  Hope this helps.

[Matt_Fraser]

Srikanth,

Joshua is referring to newly updated recommendations from SAP and Microsoft about settings for this parameter on modern servers with high numbers of CPU cores. The article he's referring to is http://blogs.msdn.com/b/saponsqlserver/archive/2015/04/29/max-degree-of-parallelism-maxdop.aspx. The recommendation also appears in the updated version of Note 1702408 (Configuration Parameters for SQL Server 2012).

In a nutshell, with SQL Server 2012 or 2014, if your server has 16 or more CPU cores, you can consider increasing MAXDOP to 2, or even 3 or 4 if you have 64 or more cores. For fewer than 16 cores, 1 is still the recommended setting, as it is for older releases of SQL Server.

0 is still not recommended, as that is essentially unlimited.

Dennis, that's a good suggestion for finding a good test, and also about having to reinitialize the procedure cache -- that isn't mentioned in the article that I recall, but it makes sense.

Joshua, once you've gathered some more data, you should write up a blog post with your results and observations.

Cheers,

Matt

[former_member206857]

I'm still "benchmarking" or playing with this.

I'm not ready to release my tests but as to know I'm impressed.

if used properly and you have a good handle on your server, you can really use this.

As i'm doing this on the side, I will be done likely next week...

[Former Member]

Have you tried to set to '0' in SSMS --Advanced options?

[luisdarui]

There are some BW queries that can put the MAXDOP under pressure. Unfortunately, I don't have any of them to test it.