9

What are the best practices with setting parallelism in general? I know that SQL Server defaults to 0 to use all available processors, but in what instance would you want to change this default behavior?

I remember reading somewhere (I'll have to look for this article) that for OLTP workloads you should turn off parallelism (set maxdop to 1). I don't think I completely understand why you would do this.

When would you keep maxdop up to SQL Server (0)? When would you turn off parallelism (1)? When would you explicitly state the maxdop to a particular number of processors?

What causes parallelism?

5 Answers5

12

You usually don't want to disable parallelism as that will also disable it for admin tasks. Your best bet is to fix the queries that are causing the parallelism through adding or fixing indexes or through making full on schema changes.

Based on your updated questions...

Some people will change MAXDOP to 1 for vendor built applications because they can't control the database or schema and they don't want a single query to take over the entire system.

Personally I always keep MAXDOP at 0 except for some rare cases.

Parallelism is caused by a single operation within an execution plan having an execution cost which goes over a pre-set setting (the cost threshold for parallelism setting). When this happens the SQL Server will kick in parallelism so that it can multi-thread the request in an attempt to speed up the process. The default value for the cost threshold for parallelism is 5. In many OLTP platforms you'll want to raise that up to 30 or 40 so that parallelism only kicks in for the really expensive queries.

mrdenny
  • 26,988
  • 2
  • 42
  • 81
4

I've never seen the need to switch off or modify any parallelism settings in all my time with SQL Server (last millennium, SQL Sever 6.5)

Following on from @StanleyJohns answer...
An OLTP system with short, sharp queries should never hit the cost threshold ("cost threshold for parallelism") so it shouldn't matter. If you have some queries that do go parallel, then why would you restrict it based on something unproven

I've yet to see a pure OLTP system too. At the extreme, maybe there are, but the average system has reporting on it too; whether intra-day or overnight. These queries are more likely to go parallel and benefit from it.

With so many CPU cores available today there is arguably a case to set the global "max degree of parallelism" if you can measure and notice a difference.

Like I said, my suggestion is to do nothing. Similar to @mrdenny, but I'd include "there is no such thing as a pure OLTP system"

Saying that, there may be some mileage is disabling hyperthreaded cores at the BIOS level but that's a different question...

Also, please see

Community
  • 1
gbn
  • 69,809
  • 8
  • 163
  • 243
3

What causes parallelism?: There is a setting called cost threshold for parallelism. Once this threshold is exceeded then parallelism is used (if prerequisites are met).

The nature of an OLTP systems is to have a large number of quick and short transactions. Using parallelism sometimes increases the query processing time, as the query will be split up to be processed in parallel, and then stitched back together before being returned. Hence you will see suggestions to set maxdop at 1.

One benefit of setting maxdop at 1 is that parallelism is turned off by default, but you can enable it at the query level using query hints.

For data warehouse systems or OLAP systems where large result sets are returned, there may be a benefit to splitting the query using parallelism. This allows the query to harness the available cores to chop down the query processing time.

StanleyJohns
  • 5,962
  • 2
  • 21
  • 44
2

I have seen a complex query split into multiple processes that takes hours to execute - you can usually see this in sp_who2 as multiple entries with the same spid.

Changing it to maxdop 1 and the query executed in less than a minute.

The lesson here is that the engine doesn't always get it right when it comes to parallelism.

Jimbo
  • 834
  • 5
  • 6
0

We have lots of servers with 4 cores and some with 8 cores. I recommend with such few cores that parallelism (MAXDOP) be set to 1 so to avoid waits on CPU (and also timeout issues) for users of OLTP systems. For OLAP or reporting servers, with such few cores, I recommend setting MAXDOP to 2 and setting Cost Threshold to 30 (this may be higher or a bit lower for your scenario) so that only the heaviest queries will use parallelism.

Todd
  • 1