2

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 ?

  • 1
    You might find the post brentozar.com/archive/2013/09/… to be helpful in adjusting the Maximum Degree of Parallelism and the Cost Threshold for Parallelism. – RLF Nov 6 '15 at 21:04
1

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.