You install your brand-new SQL Server using the installation wizard, and at the very end you hit the Finish button. Woohoo – now we can go into production with our server! Sorry, but that’s not really the truth, because your brand-new SQL Server is misconfigured by default!

Yes, you read that correctly: a default installation of SQL Server is misconfigured in so many different areas. In today’s blog posting I want to show you the 3 configuration options that you have to change immediately after the installation for faster performance. Let’s start!

Max Server Memory

Disclaimer: If you run your SQL Server these days on a 32 bit system, please throw away your hardware, buy a 64 bit system, install the 64 bit version of SQL Server, and continue reading here.

By now you should have a 64 bit SQL Server in front of you. 64 bit means that you can theoretically address a main memory size of 2^64 = that is 16 Exabytes (1 billion gibabytes)! Because of this huge amount of memory, computer vendors currently limit the address bus of a 64 bit system to “only” 48 bits – the whole 64 bits doesn’t really make sense. With an address space of 48 bits you can address up to 256 TB of memory – that’s still a huge amount of space.

You can use the Max Server Memory configuration option to configure how much memory SQL Server can consume. The following picture shows this configuration option after a default installation of SQL Server on a 64 bit system.

As you can see from the previous picture, SQL Server is configured by default to consume up to 2147483647 MB – that is 2 Petabytes! Hmm, with an address bus of 48 bits we can physically only address up to 256 TB of memory, and now SQL Server can consume up to 2 Petabytes? Something is wrong here… The Maximum Server Memory setting is just the largest possible 32 bit integer value – 2147483647. Nothing more. So SQL Server can consume more memory that can be addressed physically? Not really a good default configuration. SQL Server can eat up your whole physical memory by default!

You should always change this configuration option, so that you also give your OS some memory to live and breathe. Normally (without any other programs/processes on the server) you should give the OS at least 10% of the physical memory. This means that you have to lower the Max Server Memory setting. With 64 GB of physical memory I would configure the Max Server Memory setting with around 56 GB, so that the OS can consume and work with the remaining 8 GB.

Cost Threshold for Parallelism

The next configuration option that you have to change has to do with how SQL Server handles parallelism. Parallelism means that SQL Server is able to run operators in an execution plan across multiple worker threads. The goal of parallelism is to improve the throughput of your queries. The first configuration option that influences parallelism in SQL Server is the so-called Cost Threshold for Parallelism:

The number that you configure here defines the query costs at which the Query Optimizer tries to find a cheaper parallel execution plan. If the found parallel plan is cheaper, then this plan is executed, otherwise the serial plan is executed. As you can see from the previous picture, a default configuration of SQL Server uses a cost threshold of 5. When the query cost of your serial plan is larger than 5, then the Query Optimizer runs the query optimization again to find a possible cheaper parallel execution plan.

Unfortunately a cost factor of 5 is a very small number these days. Therefore SQL Server tries to parallelize your execution plans too soon. But parallelism only makes sense when you deal with larger queries – like in a reporting or Data Warehousing scenario. In a pure OLTP scenario a parallel plan is an indication of a bad indexing strategy, because when you have a missing index SQL Server has to scan your complete Clustered Index (in combination with a Filter or a residual predicate), and therefore your query costs will get larger, they cross the cost threshold, and finally the Query Optimizer will give you a parallel plan. And people always get worried when they see parallel execution plans! But the root cause was just a missing Non-Clustered Index.

During my various consulting engagements and SQL Server Health Checks, I always recommend a Cost Threshold for Parallelism of at least 20 or even 50. That way you can make sure that SQL Server only tries to parallelize larger queries for you. And even if you have a parallel plan in front of you, you should think about whether you can make this query plan cheaper by adding a supporting Non-Clustered Index. And as I have already covered in a previous SQL Server Quickie, CXPACKET waits are not an indication that you have problems with parallelism in your system!

Max Degree of Parallelism (MAXDOP)

When an execution plan goes parallel in SQL Server, the Max Degree of Parallelism defines how many worker threads each parallel operator in the execution plan can use. The following picture shows the default configuration of this option.

As you can see, SQL Server uses the default value of 0. This value means that SQL Server tries to parallelize your execution plan across all CPU cores that are assigned to SQL Server (by default all cores are assigned to SQL Server!). As you might expect this setting also doesn’t make sense, especially when you have a larger system with a huge amount of CPU cores. Parallelism itself introduces overhead, and this overhead gets larger and larger as you use more and more worker threads.

A recommendation is to set the Max Degree of Parallelism to the number of cores that you have in one NUMA node. Therefore SQL Server tries to keep parallel plans in one NUMA node during query execution which also improves performance.

Sometimes you will also see suggestions to set the Max Degree of Parallelism option to 1. This is a really bad advice, because this makes your *complete* SQL Server single-threaded! And even maintenance operations (like Index Rebuilds) are executed single-threaded, and this will seriously hurt the performance! Of course there are also some “award winning” products which dictate that you use a MAXDOP of 1…

Summary

After you have installed your SQL Server, the real work for the DBA begins: you have to configure your SQL Server installation to your hardware layout. As you have seen in this blog posting, the default configuration of SQL Server is simply misconfigured by default. Therefore it is very important to change some configuration options immediately after the installation. I have already seen SQL Servers in production with the default options that I have mentioned here, because they “will be configured later”. And “later” never ever happened…

So please do yourself a favour today, and configure your SQL Server for maximum performance and throughput!

I posted a message on a facebook group what values should be assigned to cost Threshold for Parallelism and MAXDOP as I don’t see any guidelines or articles to determine a value. Many of the members replied back as “leave it alone” unless you have a problem. My problem is CPU running at 80% and seeing cxpackets . Thank you for your quick note on cxpackets. Is there a way to prove cost Threshold for Parallelism and MAXDOP should be changed ?

Thanks for your comment.
As I have written in the blog posting, I would set the Cost Threshold at least on 20 or even 50.
And the MAXDOP should be set to the number of cores that you have in one NUMA node.

If your CPU usage is at 80% and you see CXPACKET waits, you will normally have problems with your indexing strategies.
Just check for expensive queries (high I/O, CPU consumption) and try to make them cheaper with additional (helpful!) Non-Clustered Indexes.
Trust me – your CPU usage and your CXPACKET waits will go down!
As I have mentioned in my SQL Server Quickie about CXPACKET waits (see http://www.SQLpassion.at/archive/2014/12/01/sql-server-quickie-20-cxpacket-waits), they don’t tell you anything, because the coordinator thread always produces CXPACKET waits.

Just want to mention that setting Max DOP to 1 is not always bad and sometimes it is a must. I have experienced “dop 1” scenario in the first hand several years ago. On the other hand, the benefit of the parallel execution is way non-significant compared with performance stability. I agree with the other two configuration recommendation though.

Thanks for your comment.
If you want to set a MAXDOP of 1 for your workload, you can also use other features in SQL Server – like Resource Governor (Enterprise Edition only!).
The problem that I have with a MAXDOP of 1 on the instance level is that *everything* runs single-threaded, even your Index Rebuild operation that would benefit from parallelism.

Resource governor really does not solve the scenario (i.e. performance stability) where max dop needs to be 1.
This configuration setting cannot be said that it should NOT be 1. Actually, as I pointed out before, it needs to be 1 SOMETIMES. Believe it or not, I was once a “hater” of setting dop=1, but I was convinced after I tried hard to make DOP 1 and still cannot achieve my goal. In short, DOP = 1 solved my performance stability issue with almost no noticeable benefit lost.

let say i have a server has 4 cpus and i don’t update the Max Degree of Parallelism (MAXDOP) and leave it as default 0
This means that SQL Server tries to parallelize the execution plan across all CPU , is this bad?

With just 4 cores SQL Server will parallelize a parallel execution plan across the 4 cores – correct.
In that case it wouldn’t make a huge difference to the default option of MAXDOP, but we want to be as explicit as possible!
Imagine your SQL Server is virtualized (ouch…), and your VMware admin (what is HyperV?) assigns at one day more cores to your VM…

Thanks for your comment.
Yes, you are absolutely right – TempDb is also misconfigured by default!
I just had to make sure to use the 3 most prominent configuration changes – otherwise I would have written a whole book instead of a blog posting 😉

SharePoint recommends setting the MAXDOP to 1 for the whole SQL server, as well as turning off auto update statistics, which might both sound bad. But the application itself manages statistics, and keeping each individual query single threaded when there may be hundreds or thousands of concurrent queries coming from multiple web front ends is actually a good scaling technique for that workload. As with all things SQL, sometimes the answer is more complex than “never do this”

That doesn’t mean every SharePoint farm should do so. But, that particular advice comes from testing for a specific workload, aimed at the (utterly awful and poorly indexed) DB SP is using, and I would recommend testing your workload with all their settings as recommended as a bsseline, then changing them one at a time to benchmark the effect

Thanks for your comment. The problem is that SharePoint requires a MAXDOP of 1 at the instance level. People normally have additional database on an instance – where the instance is not dedicated to SharePoint. So it also influences other databases. And what you do about your Index Rebuilds? They are also running single-threaded…

It would be much better to recommend Resource Governor to separate the SharePoint workload, and only apply a MAXDOP of 1 to that specific workload.

I agree with you, if other things are on there. But if you’re at the size that you really need these settings, you absolutely should not have anything else running on SPs DB server, and in fact often there will be two SQL servers dedicated to SharePoint, one for the search DBs, and another for content DBs

Hello and great article!!
I just wanted to add a comment on the Max/Min memory. When the server has more or less memory then 64GB then use this formula to calculate MAX/MIN memory.
1. Reserve 1 Gigabyte (GB) of RAM for the Operating System (OS)
2. Reserve 1GB of RAM for each 4GB of RAM installed from 4 – 16 GB
3. Add 1GB of RAM for every 8GB of RAM above 16GB’
4. For MIN memory setting, it is a good start to set to half the total MAX memory.

I have created a script the memory setting at my blog “http://sqlsurgeon.blogspot.ca/2011/12/automatically-calculate-max-memory.html”

SQL First Aid Kit Sign up for free

By confirming your subscription, we will store your email address and send you additionally emails about our upcoming trainings and about our published blog postings.

Here is some feedback

Bob from Zoetermeer, Netherlands

"The SQL Server Query Tuning Workshop helped me as a developer to realize how important a good indexing strategy is for a responsive application. In my opinion every developer, working with SQL Server, should know how the indexes are build up, and how the query optimizer is getting to his query plan."

5.0

2015-01-22T12:38:33+00:00

"The SQL Server Query Tuning Workshop helped me as a developer to realize how important a good indexing strategy is for a responsive application. In my opinion every developer, working with SQL Server, should know how the indexes are build up, and how the query optimizer is getting to his query plan."

https://www.sqlpassion.at/archive/testimonials/bob-from-zoetermeer/

Roger from Hertogenbosch, Netherlands

"During the workshop I have learned a lot about the query optimizer internals and how I can use that knowledge to write optimal performing queries. Klaus obviously knows a lot about these topics and is able to teach them very well. I can’t wait to attend one of his workshops in the near future."

5.0

2015-01-13T15:28:08+00:00

"During the workshop I have learned a lot about the query optimizer internals and how I can use that knowledge to write optimal performing queries. Klaus obviously knows a lot about these topics and is able to teach them very well. I can’t wait to attend one of his workshops in the near future."

Thomas from St. Margrethen, Switzerland

"I was lucky enough to attend the 4 day long SQL Server Query Tuning Workshop from Klaus in Zurich. He has presented in a very easy way the performance related internals of SQL Server that will help you to write better performing SQL queries. Based on that knowledge I finally know now why sometimes queries are fast, and sometimes are slow. It was an amazing workshop, which makes the day-to-day work with SQL Server now very easy."

5.0

2015-01-13T15:23:09+00:00

"I was lucky enough to attend the 4 day long SQL Server Query Tuning Workshop from Klaus in Zurich. He has presented in a very easy way the performance related internals of SQL Server that will help you to write better performing SQL queries. Based on that knowledge I finally know now why sometimes queries are fast, and sometimes are slow. It was an amazing workshop, which makes the day-to-day work with SQL Server now very easy."

Arun from London, United Kingdom

"My day-to-day writing of SQL has gone up a level – I am able to finely tune individual statements or underlying database objects to get the kind of query plan I want. Moreover I know now what sort of plan I am looking for. I have been already able to use this knowledge to address performance issues that came up this week in capacity testing."

5.0

2015-01-13T15:19:20+00:00

"My day-to-day writing of SQL has gone up a level – I am able to finely tune individual statements or underlying database objects to get the kind of query plan I want. Moreover I know now what sort of plan I am looking for. I have been already able to use this knowledge to address performance issues that came up this week in capacity testing."

Bernd from Monheim, Germany

"After attending the 4 days long workshop you know very exactly how SQL Server executed SQL queries on the logical and physical level through execution plans. In addition you will know how you can improve queries by applying the right indexing strategy. This in-deep knowledge was very well explained and presented by Klaus."

5.0

2015-01-13T15:22:12+00:00

"After attending the 4 days long workshop you know very exactly how SQL Server executed SQL queries on the logical and physical level through execution plans. In addition you will know how you can improve queries by applying the right indexing strategy. This in-deep knowledge was very well explained and presented by Klaus."

Ina from Oberhaching, Germany

"Anyone faced with the task to implement queries on complex ERP data structures – for reporting, providing views, or for statistical analysis – comes quickly to its limits without knowledge of the operation of SQL Server. The workshop provides an excellent insight into the technical world of SQL Server, explained with many examples of queries and their optimization."

5.0

2015-01-13T15:21:05+00:00

"Anyone faced with the task to implement queries on complex ERP data structures – for reporting, providing views, or for statistical analysis – comes quickly to its limits without knowledge of the operation of SQL Server. The workshop provides an excellent insight into the technical world of SQL Server, explained with many examples of queries and their optimization."

Filip from Beerse, Belgium

"This training on SQL Server performance tuning was extremely interesting. Klaus taught me lot of things I was not aware of. I especially loved the tips he gave on common mistakes and wrong concepts about SQL Server. Such information, coming from an independent expert (“expert” is still very understimated when talking about Klaus’ knowledge), is a lot more valuable than spending hours reading and googling. This training has a high return on investment and anyone who is seriously working with SQL Server should attend this workshop."

5.0

2015-01-13T15:13:34+00:00

"This training on SQL Server performance tuning was extremely interesting. Klaus taught me lot of things I was not aware of. I especially loved the tips he gave on common mistakes and wrong concepts about SQL Server. Such information, coming from an independent expert (“expert” is still very understimated when talking about Klaus’ knowledge), is a lot more valuable than spending hours reading and googling. This training has a high return on investment and anyone who is seriously working with SQL Server should attend this workshop."

Wim from Heverlee, Belgium

"The SQL Server Performance Tuning & Troubleshooting Workshop exceeded the high expectations I already had. All the separate bits and pieces of SQL knowledge we gathered through the years, were properly tied together by Klaus. He provided us with all the necessary knowledge to tackle the performance issues we will surely encounter in our day to day business. Highly recommended!"

5.0

2015-01-13T15:08:29+00:00

"The SQL Server Performance Tuning & Troubleshooting Workshop exceeded the high expectations I already had. All the separate bits and pieces of SQL knowledge we gathered through the years, were properly tied together by Klaus. He provided us with all the necessary knowledge to tackle the performance issues we will surely encounter in our day to day business. Highly recommended!"

Carla from Heverlee, Belgium

"The workshop exceeded my expectations! The complete workshop was well structured, all topics were explained very clear with a lot of excellent prepared and documented demos. I definitely will use the knowledge and documentation that Klaus shared with us during the workshop as starting point and guide for my future work."

5.0

2015-01-13T15:10:40+00:00

"The workshop exceeded my expectations! The complete workshop was well structured, all topics were explained very clear with a lot of excellent prepared and documented demos. I definitely will use the knowledge and documentation that Klaus shared with us during the workshop as starting point and guide for my future work."

Sedigh from Veenendaal, Netherlands

"I found the SQL server Performance Tuning workshop very useful. No matter you are a DBA, developer or tester, Klaus explains the materials the way that it is understandable for everyone. I recommend this workshop, it worths 5 stars plus!"

5.0

2015-04-21T18:47:32+00:00

"I found the SQL server Performance Tuning workshop very useful. No matter you are a DBA, developer or tester, Klaus explains the materials the way that it is understandable for everyone. I recommend this workshop, it worths 5 stars plus!"

https://www.sqlpassion.at/archive/testimonials/sedigh/

Adrian from London, United Kingdom

"It does not matter how long you have been working with databases, there is so much in this course that you are guaranteed to learn a great deal. I have been working with databases for 20 years and I have never gained as much knowledge of database internals in such a short period. I have been searching for some time for a course like this and this is packed with valuable techniques and tips."

5.0

2015-07-03T09:49:03+00:00

"It does not matter how long you have been working with databases, there is so much in this course that you are guaranteed to learn a great deal. I have been working with databases for 20 years and I have never gained as much knowledge of database internals in such a short period. I have been searching for some time for a course like this and this is packed with valuable techniques and tips."

https://www.sqlpassion.at/archive/testimonials/adrian-from-londonuk/

Michael from Stuttgart, Germany

"Klaus presented his SQL Server Performance Tuning Workshop on a very technical and understandable level. All topics of the agenda were covered and structured in an excellent way. Klaus demonstrates with his examples complex topics in a very simple way. What can you use from this workshop in your day-to-day life? Everything!"

5.0

2015-11-17T16:09:19+00:00

"Klaus presented his SQL Server Performance Tuning Workshop on a very technical and understandable level. All topics of the agenda were covered and structured in an excellent way. Klaus demonstrates with his examples complex topics in a very simple way. What can you use from this workshop in your day-to-day life? Everything!"

Dieter from Kirchheim-Heimstetten, Germany

"The 5 days were very interesting and informative for me. The various subjects were presented by Klaus in a very clear way without getting bored. Every possible question was answered by Klaus with a great explanation. I use now the acquired knowledge in my daily work to optimize my SQL Server installations."

5.0

2015-11-26T10:01:11+00:00

"The 5 days were very interesting and informative for me. The various subjects were presented by Klaus in a very clear way without getting bored. Every possible question was answered by Klaus with a great explanation. I use now the acquired knowledge in my daily work to optimize my SQL Server installations."

Markus from Diepoldsau, Switzerland

"In the company I am working for, I have to guarantee the 100% availability of our ERP system and to increase its performance. With this outstanding course I am now even more able to master these tasks day-by-day. More than before I can now identify bottlenecks and provide solutions to eliminate them."

5.0

2015-12-08T14:11:01+00:00

"In the company I am working for, I have to guarantee the 100% availability of our ERP system and to increase its performance. With this outstanding course I am now even more able to master these tasks day-by-day. More than before I can now identify bottlenecks and provide solutions to eliminate them."

Claudio from Stäfa, Switzerland

"The workshop was very well structured, a very good mix between theory and practical examples. I had a lot of light bulb moments and it gave me a much better feeling for the practical techniques that can be used in the field and how things are related. I’m looking forward to apply what I’ve learned in the near future. Make no mistake, the course and topics are challenging, but your effort will be rewarded!"

5.0

2015-12-08T14:12:50+00:00

"The workshop was very well structured, a very good mix between theory and practical examples. I had a lot of light bulb moments and it gave me a much better feeling for the practical techniques that can be used in the field and how things are related. I’m looking forward to apply what I’ve learned in the near future. Make no mistake, the course and topics are challenging, but your effort will be rewarded!"

Michail from Rotkreuz, Switzerland

"Five days of pure SQL knowledge were described in excellent manner. The day after I was able to understand the behavior of my databases and its execution plans in a much better way. I also know how important a good index strategy can be and how I can handle the locking und blocking behavior within my databases. Klaus explained all the necessary knowledge in a simple way and showed everything with a lot of good examples. Thank you."

5.0

2015-12-11T10:03:34+00:00

"Five days of pure SQL knowledge were described in excellent manner. The day after I was able to understand the behavior of my databases and its execution plans in a much better way. I also know how important a good index strategy can be and how I can handle the locking und blocking behavior within my databases. Klaus explained all the necessary knowledge in a simple way and showed everything with a lot of good examples. Thank you."

Siegfried from Munich, Germany

"A lot of important information were presented in the five days. Klaus knows every question an answer and has it always demonstrated with a concrete example. For all people who have performance problems with SQL Server, the workshop is a recommendation!"

5.0

2015-12-18T07:45:12+00:00

"A lot of important information were presented in the five days. Klaus knows every question an answer and has it always demonstrated with a concrete example. For all people who have performance problems with SQL Server, the workshop is a recommendation!"

Mark from Montfoort, Netherlands

"In this workshop Klaus manages to cover everything you need to know about the SQL Server engine. With real-life examples it also becomes easy to follow.
I would highly recommend this course to every DBA or developer."

5.0

2016-04-18T14:48:31+00:00

"In this workshop Klaus manages to cover everything you need to know about the SQL Server engine. With real-life examples it also becomes easy to follow. I would highly recommend this course to every DBA or developer."