Sql-server – Ideal MAXDOP & CPUs – how to trace for parallel queries

maxdopsql server

There is a discussion going on in our company on what the ideal CPU count and Max Degree of Parallelism are for a 3rd party database server.

The server has 12 CPUs, 32GB RAM and all database sizes add up to < 30GB so they can all fit in memory (I tried to force this by doing a select * from every table). On certain payroll days, the CPU gets maxed out to 100% for a few seconds.

MAXDOP was originally set to the default 0. We later changed it to 8 based on several 'best-practices' articles. However the vendor suggests to change it to 1 (no parallelism), while others suggest changing it to 4, so that one run-away query doesn't hog most of the CPUs.

I'd like to find out how many CPUs are actually being used by queries. There is a Degree of Parallelism event in Profiler. The BinaryData column says :

0x00000000, indicates a serial plan running in serial.
0x01000000, indicates a parallel plan running in serial.
>= 0x02000000 indicates a parallel plan running in parallel.

What does "parallel plan running in serial" mean ?
I see a lot of 0x01000000, and a few 0x08000000's in my trace. How can i determine whether one query is hogging CPUs and if reducing it to 4 will help ?

Best Answer

The post on Five SQL Server Settings to Change:

http://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/

Suggests the following:

  1. Maximum Degree of Parallelism: Limit parallelism to the number of physical cores in a single CPU socket,
  2. Cost Threshold for Parallelism: Start with 50 then tune up or down depending on your need.

Also: A parallel plan running in serial means that although a parallel plan was created, the needed resources for running parallel are not available. So the plan will serially runs sections of the parallel plan until finished.

Related Question