Threading The CPU

From a slang term for the clothes you wear, through what makes up those clothes, to how a computer process work, they are all called threads. In this post we will discuss what threads are in compute terms, how they relate to SQL Server, and a ramification or two around the use of threads by SQL Server. 

The basic question to ask at this point is, from a compute perspective, what is a thread?  In simple terms a thread is the smallest set of instructions designed to be independently managed by a scheduler.  Perhaps that isn’t quite as simple as was initially indicated, so let us parse the statement and explain a few things. 

It is not uncommon for database administrators to lump schedulers, workers and CPU cores into a single, interchangeable pile of words.  However, CPU cores are the physical subsections of the CPU on which code runs, while workers are the processes which run on those cores, management of those workers is done by a piece of software called a scheduler.  

When hyperthreading is enabled, a single processer core can process two threads simultaneously.  If hyper threading is enabled each physical core registers as two logical cores, without hyperthreading there is a 1:1 ratio of physical to logical cores. 

Note, documentation may interchange the terms logical core and logical CPU with no practical effect.  You may now be wondering if processes and threads are the same thing, and the answer is generally no. 

Typically, a thread is subset of a process rather than the entire process, however it is possible, in certain circumstances, that a thread could be the whole process.  Multiple threads within a process are extremely efficient in switching contexts as the switch happens in the CPU registers without having to engage the kernel. 

Context switching in this manner lends itself to better overall performance. In SQL Server these benefits are seen in how the overall product runs, which means there is positive impact to queries.

However, there is a potential downside as we look at multithreaded, aka parallel queries.  The downside I have most often encountered is thread starvation, which can be seen via a read in THREADPOOL waits.  The threadpool wait means that the query is waiting for enough threads to be available in the threadpool. 

While this may seem obvious, the real question is what is the threadpool.  We can think of the threadpool as a holding area for unused threads.  Generally, the number of threads in the pool is the difference between active threads and max workers.

Max workers is a setting within SQL Server, that is often not touched nor should it be. By default the value is zero, which allows SQL Server to set the maximum number of threads. The following table, from the Recommendations section of the Microsoft document titled “Configure Max Worker Threads” shows max workers based on CPU count. 

In this case the number of CPUs references the number of logical CPUs.  From the same document referenced above, this is the formula by which max workers is calculated Default Max Workers + ((logical CPUs – 4) * Workers per CPU).  You will notice that the architecture (32-bit vs. 64-bit) makes a difference as does the SQL Server version, down to the service pack level.

Returning to our thread starvation issue and painting with a broad brush, the situation generally occurs, at least in my experience, when a query or set of queries is called frequently, often near simultaneously, and query plan unnecessarily exceeds the cost threshold of parallelism.  The result is query plan in which multiple threads are assigned but only one of them is doing the work.

Remediation the situation falls roughly into two stages. The first stage is to connect to the server via the dedicated admin connection (DAC) as SQL Server will reserve resources to allow the DAC to connect.  Remember, you likely will not be able to connect otherwise, as there are no threads to process the connection request. 

Once connected via the DAC problematic queries need to be identified, including capturing query plans if possible.  After identification of the problematic query(ies) you will need, if possible, to kill off some of the workload and to free up some threads.

Stage one is largely about identifying the problem and creating enough “breathing room” for the server not to lock up.  Queries in hand we can move to a non-production system and work on mitigating the actual problem.  At this point we are now performance tuning. 

Is the query returning designed in such a way that the predicate is filtering as much as possible?  Are any problems, such as implicit conversion, preventing proper index utilization?  Now, about the indexes being used, do they need to be adjusted or is there a need to create new indexes to support this query? 

Now, once you have reached this point you likely see some improvement, but may well still see the query going unnecessarily parallel, what is the next thing to look at?  The thing to look at is statistics.  First question how often are you updating the statistics and is it frequent enough for the rate of change in the table(s) being queried? 

The greater the rate of change the better the chance that out of date statistics are a contributing factor.  In addition to statistics being out of date it is possible the sample size needs to be adjusted. 

In summary we have discussed the basics of what a thread is and how it differs from a process.  We discussed how threads, specifically the lack of them in the threadpool can impact SQL Server.  Additionally, we hit the highpoints of investigating and remediating the threadpool problem.  With this introduction to threads you should now be able to look at SQL Server and how it processes with some new perspective.

Explore IDERA's SQL Server solutions.

Written by Peter Shore/posted by Zak Cole