When Should We Increase The Max Worker Threads?

Why will we Increase MAX Worker Threads ? When all are active with long running queries, SQL Server might appear unresponsive until a worker thread completes and becomes available.

If SQL Server reaches its maximum capacity of 960 threads [for 32 processors / settings default value to 0] and does not take any more connections as it has run out of worker threads SQL Server may seem unresponsive.

It is not a defect of SQL Server but to improve SQL server performance. One should consider using Indexes in SQL Query which is running, or measure the Query cost and improve index or query being executed.

If nothing can be done from the application or query end, you can manually set the worker thread to a larger number, but it will significantly slow down your SQL Server, as now it will use Worker Thread more than your CPU can handle. Or you might consider adding processor resources to your Server.

Use Below Tsql To Monitor And Set Max Worker Threads As Per Your Requirement.

USE master; //choose the database you want to set max worker threads for. master database will set it at instance level.
GO
EXEC sp_configure 'show advanced option'//shows the current value of the advanced options
GO
EXEC sp_configure 'show advanced option', '1'; //enables the advanced options
GO
RECONFIGURE WITH OVERRIDE; //reconfigure the change
GO
EXEC sp_configure 'show advanced option' //verify that the advanced options have been enabled
GO
EXEC sp_configure //shows all the advanced options available
GO
EXEC sp_configure 'max worker threads' //shows the current value of max worker threads
GO
EXEC sp_configure 'max worker threads', 3500; //choose the value appropriately 
GO
RECONFIGURE WITH OVERRIDE; //reconfigure the change
GO 
EXEC sp_configure 'max worker threads' //verify that max worker threads reflect the new value
GO
EXEC sp_configure 'show advanced option', '0'; //disable the advanced options
GO
RECONFIGURE WITH OVERRIDE; //reconfigure the change
GO

To See The Current Connections And Current Max Worker Threads.

SELECT
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame

SELECT SUM(current_workers_count) as [Current worker thread] FROM sys.dm_os_schedulers

Do Lots Of Connections Slow Down Your Sql Server? [ Increase MAX Worker Threads ]

I sometimes hear database administrators say, “This database must be involved in our performance problem: it has too many connections.”

Lots of connections might cause a problem for your SQL Server, or it might not. The good news is that there’s a way to clearly tell if they’re dragging down performance or not.

How Can I Prove That Lots Of Connections Are Causing A Performance Problem?

SQL Server can manage a lot of connections without too many issues. Yes, it’s better if an application cleans up its connections over time. We recommend tracking the User Connections performance counter, as patterns in this counter may coincide with other events and be useful information.

But just having a high amount of connections doesn’t prove that was the cause of performance problems. For a smoking gun as to why SQL Server is slow, look in two places:

• SQL Server Wait Statistics (look for THREADPOOL waits)
• The SQL Server Error Log (look for the creepy error below)

Threadpool Waits: A Sign Of Trouble

When lots of connections want to run queries at the same time, SQL Server may need to allocate more worker threads. This process can be slow, and at a certain point SQL Server may reach the maximum worker threads allowed for your configuration.

If this happens on your server, when you query your wait statistics since startup.

“New Queries Have Not Been Picked Up By A Worker Thread”: Thread Starvation

When this gets really bad, you may see a nasty error message in your SQL Server Error Log. This message says something like this:

New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 60 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the “max worker threads” configuration option to increase number of allowable threads, or optimize current running queries.

The message neglects to mention a few critical things:

• Microsoft doesn’t recommend that you change max worker threads, as a general rule
• The amount of worker threads needed has a lot to do with your parallelism settings (and changing parallelism settings is far more common than the amount of worker threads)
• Periodic problems like locking and blocking can also drive up worker threads. Raising worker threads might just mask the real root cause of a blocking problem!

What To Do If You Have Threadpool Waits Or Thread Starvation

First, don’t panic. Avoid changing any settings before you know exactly what they’ll do.

Here’s where to start:

How’s your parallelism? If you have the “max degree of parallelism” setting at the default value of 0 (or a very high number), a good first step is to learn about CXPACKET waits and options for configuring parallelism.

Do you have lock waits? If you have high lock waits (query), consider which tables have the most blocking and how to track down the queries involved.

Have you identified the most frequently run queries in the problem period? Good indexing or improved TSQL for those queries can dramatically reduce needed threads.

What do the perf counters say? Take a look at those performance counters we recommend to see if there’s a jump in the number of connections when the problem occurs or not.

Approaching the problem this way dispels the mystery of whether all those connections really are the problem (or not).