Do Lots of Connections Slow Down Your SQL Server?

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.

Lots of connections can be confusing

If you use old built in tools like sp_who2 to see what’s happening in your SQL Server, many connections make it hard to see what’s going on. Firing up my test instance, I get back more than 45 rows from sp_who2 without any applications running at all. Sorting through that can be a mess.

For this reason, I’m not a fan of sp_who2. My usual question for the SQL Server isn’t “who’s connected?” I usually want to know other things:

• What’s making SQL Server wait right now? I like sp_BlitzFirst® to answer that.
• What’s running right now, and how long has it been running? I like sp_whoisactive by Adam Machanic to answer that.

Both of these tools help keep lots of connections from slowing YOU down as you look at your SQL Server.

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:

1. SQL Server Wait Statistics (look for THREADPOOL waits)
2. 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, you will see “THREADPOOL” waits in the results from a live sample of sp_BlitzFirst®, or 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), look into 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. (Handy tools here.)

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).