Do Lots of Connections Slow Down Your SQL Server?

Threads are important.

Threads are important.

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:

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:

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

Previous Post
What Amazon RDS for SQL Server Users Need to Know about Multi-AZ Mirroring
Next Post
Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)

2 Comments. Leave new

  • My server is that slow where sp_who2 is also not giving result. What could be possible issue that sp_who2, sp_BlitzFirst, sp_whoisactive is not returning any result and running too slow.

    Reply
    • Bhargav – that’s a great question that we answer as part of our consulting. You can click Consulting up at the top of the page to start that process.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}