During the last twenty-some years I have looked at a huge number of SQL Server instances. Most of them were brought to my attention because they were not performing as expected and I was tasked with fixing the problem.
A surprising number of these problems were easy to fix. But some of the problems were difficult and a few could not be fixed without starting over.
The problems that were easiest to fix often involved instance and database settings, particularly the default instance settings for parallelism.
Over the years SQL Server has evolved from a barely adequate database platform into a big, multi-faceted behemoth that is the equal or better than other available database products (my opinion).
Along the way many changes and improvements were made. However, many of the obsolete default settings have never been changed even as technologies changed dramatically.
Some database administrators still rely on these settings because they think that, since these settings come from Microsoft, they are the best or the safest settings. That is no longer the case for some so we are going to talk about a couple of examples where accepting the default settings might hurt you.
The first setting we are going to look at is Cost Threshold For Parallelism. This setting controls at what level of query complexity the optimizer will consider before creating a parallel plan.
The purpose of this setting is to prevent the optimizer from creating a parallel execution plan for trivial queries. It makes sense since under parallelism the optimizer will have to create two execution plans, one of them being a parallel execution plan. Then it has to compare the cost of the two and use the least-cost plan. This takes time and should be avoided with trivial queries that don’t benefit from parallelism.
The following story is amusing and it seems like a techno myth told by older geeks like me to the new generation. However it has been confirmed to me by many old-timers who should know.
Once upon a time long ago and far away there was a guy named Nick. Nick was a developer in the SQL Server gang at Microsoft and was tasked to create a scale that would reflect the ‘cost’ of a specific query. The cost was defined as how many seconds it took for a query to run ON NICK’S COMPUTER.
(Don’t laugh, this scale was established when other people were still using bamboo slide rules to work out mathematical problems.)
When it came time to decide what the default value should be, Nick, following a not very scientific method said “how about 5” the others said “OK” and then went back to playing minesweeper.
Remember that this decision was made when a typical Windows computer had 4 or fewer cpu’s and memory that was still measured in megabytes. It is still the default today when the cost of a query has nothing to do with Nick or his computer and the definition of a trivial query is very different than when this scale was created.
Microsoft now suggests in some white papers that you raise this threshold but they have not changed the default. They have a formula but most of the SQL Guru’s I know just recommend starting at 50 or higher and see how that works. I refuse to be a guru but I agree with them on this point.
Often the threshold has to be raised from there according to the balance in your load between trivial and none-trivial queries.
Max Degree of parallelism (MAXDOP) is another parallelism setting but its origin is not so bizarre as Cost Threshold For Parallelism. It simply controls how many of your CPU cores can be used to execute parallel queries. The default is zero. But in this context zero means that the optimizer can (in theory) use all of your cpu’s to execute a single long running parallel query.
The obvious problem with this setting is that a long running parallel query could tie up all of the other queries waiting for the CPU. (I don’t know if this has been fixed in recent versions, but so far as I know there is still a risk of all your processors being used for a single query. If someone knows better please tell me. I am way too lazy to dig this information out using Google.)
Setting MAXDOP to 1 will tell the optimizer not to create any parallel execution plans ever. Any other number will designate how many cores can be used overall for parallelism.
The rule of thumb now is to use half of the cores you have for parallelism. This assumes that you have 8 or more cores but nowadays who doesn’t? And if you have a HUGE number of processors you probably won’t want to dedicate half of them to parallelism. Experimentation is mandatory.