Earlier this week, I wrote a blog post about SQL SERVER – Best Value for Max Worker Count, after reading this blog post, I got quite a lots of questions via email, LinkedIn and on Twitter to share my when it is appropriate to change Max Worker Count. Before I continue this blog post, you must understand that if you are facing SQL Server Performance issues, focus on other areas first before you tweak this setting.
In my career of 10 years and with over 100s clients for Comprehensive Database Performance Health Check, I have changed the value of max worker count only 4 times to gain performance. Rest of the time, it is best to leave Max Worker Count to a default value of Zero.
Today we will see a unique scenario where I was able to change the Max Worker Count and got better performance.
Story of Black Friday Sale
There have been three instances in my career, I have encountered this situation. One of the large e-commerce organization (one of them have a single table size of 1.1 TB) was getting ready for Black Friday sales. They had already invested a huge amount of money on their server. Every year during the Black Friday sale, they get the application and database overwhelmed with the traffic and often observed downtime during their prime sales duration.
This year, they had hired me in advanced to avoid this situation. We worked through their architecture and created a separate SQL Server instance (on the different server) just to manage their shopping cart. The server was just doing one task, adding items on the shopping card based on the available inventory which we were syncing from the main server every few seconds. Additionally, the checkout process was managed on this server as well.
As soon as the Black Friday Sale went live we noticed that huge amount of traffic on the server from various end customers. Additionally, quite often people take a few minutes to fill up their cart, we ran few processes to make sure that the items which are listed in the cart are available and there is no change in the price or quantity.
Overall, the system did well in the beginning and eventually started to have a long queue of the request and end-clients were waiting for a long period of time before they were able to add items to their cart. The situation was going worse exponentially.
No CPU Pressure with High # of Connections
We checked their system and realized that they have CPU just utilized only 5% and lots of connection was waiting for the available threads. This clearly showed that our server which was extremely powerful at that point of time underutilized and underperforming due to the amount of the new clients were waiting to connect to the server.
As there were no other major wait types, and the CPU was not under pressure, I immediately increased the Max Worker Count to a higher value. Within a few seconds, new available threads started to take up work the backlog of waiting for new tasks was resolved.
Summary
If you have directly skipped to this section, let me summarized for you our unique scenario –
- Lots of new connections
- Lots of long-running queries
- No major waits types
- CPU running very very low
- Lots of connections waiting for available threads
In this situation, when I had increased Max Worker Count, we were able to get more performance. As I mentioned in my career of many years as a performance tuning experience, I have experienced this only 3 times. It is an extremely rare case when you have to increase the max worker count.
Caution
If you increase the Max Worker Count without understanding its implication on your server and without running proper investigation tests, you may end up hurting your system more. I have seen many clients who have increased the worker count and their system has stopped responding.
If you are facing SQL Server Performance Problems, before changing any configuration value just reach out to me and I will be happy to work along with you on Comprehensive Database Performance Health Check
Reference: Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
Interesting situation. Is there a situation when you might have lowered the number of worker threads?
that I have never done so far sir. I have always gone with the goal that my consultancy should help customer to speed up the system so much that they should efficiently use all the default worker threads.