Wednesday, November 27, 2013


     On my short list of SQL Server configurations that need to be set specifically to your servers needs is MAXDOP. It is the maximum number of C.P.U. that a query can use if it is going to cost more than a few seconds.
      The degree of  C.P.U. parallelism allowed by the server is often looked at like a turbo button for some of your system's heavy lifting.It can also be perceived as a freeway that has mile long backups at rush hour.
  While I have faith in this concept, many of my specific experiences have eventually whittled down to a MAXDOP of 1. This is effectively the off switch for this feature. Out of the box SQL Server set MAXDOP to zero, this is the full throttle setting. It allows any long running query to use all available C.P.U..
   It needs to be noted that SQL Server uses the configured number of C.P.U. when the cost threshold is passed. The default setting for cost threshold is 5 seconds. You need to consider what percentage of your queries run long enough to work using extra C.P.U..
   You have the tools to determine how long any query in your system takes to run. The surgeons out there will figure out what percentage of the queries take how many seconds to run. The cowboys will either estimate how many small transactions are happening versus the total time spent running large queries.
   You may read that  MAXDOP should be set to half the number of C.P.U up to 8. The question here is how likely is it that multiple long running queries will be running on the server at the same time. Too many simultaneous long running queries will cause excessive  CXPACKET or SOS_SCHEDULER_YIELD waits. You will then have to decide if you can raise the cost or if you have to lower the MAXDOP setting. Let the trial and error begin.
   The easiest place to make use of MAXDOP at its best is on your data warehouses and report servers. It is here that the processes are more likely scheduled or in sequence. Heavy lifting won't collide with other long running queries.
    The best advice when making any configuration change is to make small changes and carefully monitor the outcome. Best of Luck.

No comments:

Post a Comment