10

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?

6 Answers6

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
  • 27,106
  • 2
  • 44
  • 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
  • 70,237
  • 8
  • 167
  • 244
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,982
  • 2
  • 25
  • 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
-1

OLTP query cost.

OLTP systems typically are expected to have high concurrency. Schedulers (CPU's) need to be available for worker threads. The default query cost threshold of 5 is admittedly very low. But the goal in OLTP is to keep query cost as low as possible. Lower than 5 will be a good challenge in complex systems.

When a query cost is higher than threshold, I am not totally sure if giving it DOP of all sockets would be a good idea. Sure enough, queries higher than threshold will start using parallelism, but at the cost of worker threads for other concurrent requests.

This was a long way to say that the real goal should be to reduce query cost.

Costly queries when call parallelism, the optimizer decides to split the data into equal sets of whatever MaxDOP is configured. This decision is based on estimate and is never 100% accurate. So, these smaller datasets are usually not equal. Each runs on its own worker thread. And each worker thread handling a smaller portion of data does not necessarily run faster. In fact, optimizer spent precious time deciding and splitting data. Then it executed that same plan MaxDOP times to pull that data. You just hope that you had good covering indexes (INCLUDE clause) to avoid Key Lookup's.

After all the data is collected by MaxDOP number of threads, these smaller datasets have to be put back together. That is another resource consuming process. Moreover, since not all threads retrieved the same row counts, usually some threads complete sooner than others. The completed threads then wait for all the threads to complete before the data can be merged into one dataset.

That is where you see CXPACKETS type of waits.

So why does SQL Server then even have parallelism?

The best consumers for parallelism are data warehouses. In OLAP systems, data is bulk loaded from other systems into fact tables or if you are using Inmon approach, other semi-normalized systems. In either case, the data is coming in and needs to be populated in the data warehouse tables.

In this case, parallelism comes in handy. It splits the bulk data in MaxDOP threads and populates the destination tables in parallel.

Zeb
  • 1