Michael J. Swart

A Really Busy September

It’s been a whirlwind of a month. The first day of school, or a new semester is always a busy time for online education. And in 2020 that’s an understatement. This year, we had to answer how busy could one SQL Server get?

We’ve always approached this question using the usual techniques

• Cache like crazy (the cheapest query is the one that doesn’t have to be run)
• Tackle expensive queries (CPU, IO)
• Tackle large wait categories (so much easier said than done)
• Offload reporting queries to other servers.

This year we smashed records and faced some interesting challenges along the way. I want to talk about those challenges in the next few blog posts. This first post is about user connections. Something I’ve never really paid much attention to before now.

A Metric I’d Never Thought I’d Have To Worry About

The maximum number of user connections that SQL Server can support is 32,767. That’s it. That’s the end of the line. You can buy faster I.O. or a server with more CPUs but you can’t buy more connections.

I actually mentioned this limit in the post where I introduced Swart’s 10% rule: “If you’re using over 10% of what SQL Server restricts you to, you’re doing it wrong” In that post, I was guarded about that statement as it applied to the user connection limit. But I’d like to upgrade that to elevated.

With such a hard limit, it’s important to watch this metric carefully. You can do that with the performance counter SQLServer: General Statistics – User Connections or with this query:

SELECT   ISNULL(DB_NAME(database_id), 'Total On Server') AS DatabaseName, 
         COUNT(*) AS Connections,
         COUNT(DISTINCT host_name) ClientMachines
FROM     sys.dm_exec_sessions
WHERE    host_name IS NOT NULL
GROUP BY ROLLUP( database_id )

The Maximum is 32,767
If you haven’t changed the maximum number of user connections by some method like sp_configure 'user connections', then the default is 0 and @@MAX_CONNECTIONS will return 32,767. I think the UI for this property is a bit misleading, 0 is absolutely not equivalent to unlimited.

What Does Trouble Look Like?

The issue shows up on the client when it’s unable to establish a connection to the server. There’s a variety of errors you might see such as:

• Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server.
• A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)
• Or simply the generic:
• A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 – Could not open a connection to SQL Server)

It’s Almost Always a Symptom

It’s often a symptom and not the root cause. For example, imagine you have a busy server with lots of clients and there’s a sudden slowdown.

Perhaps the slowdown is an increase in a certain wait category like blocking (LCK_M_X) or perhaps the slowdown is an increase in CPU because of a bad query plan. Either way, queries suddenly require a longer duration. They can’t complete quickly and remain open. So any new queries that come along have to make brand new connections (because there are no available connections in the connection pool). Maybe THREADPOOL waits start to pile up. And now even more sessions are waiting and more sessions get created. Then you might reach the maximum number of connections in this scenario.

But decent monitoring will often identify the actual root cause here (the blocking or whatever). And it’s actually easy to ignore the user connection limit because it wasn’t really the bottleneck.

But Sometimes it’s the Root Cause

Once we’ve tackled all the other scaling challenges, and the demand gets cranked up to 11. Then we can see trouble.

In our case. We have an elastic scaling policy that allows us to spin up client machines in response to demand. So hundreds of web servers times the number connection pools times the number of connections in each pool can really add up fast. So those web servers scale really nicely, but without a decent sharding strategy, SQL Server doesn’t.

On one of our servers, this is where we’re sitting now, and the number of connections is uncomfortably high. There’s not a lot of room to tolerate bursts of extra activity here:

The red lines indicate 10% and 100% of the maximum connection count.

What We Can Do About It

• Monitor the number of connections that is typical for your servers using the query above for a point in time. Or use the performance counter: SQLServer: General Statistics – User Connections
• Make sure you’re not leaking any connections. This SQLPerformance post tells you how to find that.
• Use connection pooling efficiently. This Microsoft article SQL Server Connection Pooling (ADO.NET) has some great tips on avoiding pool fragmentation. The article describes pool fragmentation as a web server issue. But the tips are also appropriate to minimizing the total user connections on the server.
• Keep the variety of connection strings used by your application small. With connection pooling, connections are only made when they’re needed. But still there’s a little bit of overhead. It takes 4 to 8 minutes for an idle connection to be released by the pool. So minimizing the number of connection pools actually does help.
• Queries should be as quick as possible. Get in and out.
  • So be quick about reading the data you asked for (i.e. avoid C#’s yield if you can)
  • Offload reads as much as possible to other servers. Availability groups have read-only routing features but be careful how you implement this. If you have some connection strings that use ApplicationIntent=ReadOnly and some that don’t, then that’s two different connection pools. If you want to defer configuring AGs until after the connection strings are done, then there can be some tricky overlapping scenarios. It’s complicated, and it just highlights the importance of monitoring.
  • When tuning queries. The metric to focus on is the total duration of queries. Try to minimize that number. That’s the total_elapsed_time column in sys.dm_exec_query_stats. Or the elapsed time query in http://michaeljswart.com/go/top20. This is an interesting one. I’ve always preferred to focus on optimizing CPU or logical reads. But in this case, connection count is most sensitive to long running queries no matter the reason.

I’d love to hear about others who have tackled this problem. Let me know what strategies you came up with.

My friend pointed out an interesting RCA by Github where a

database table’s auto-incrementing ID column exceeded [maxint]. When we attempted to insert larger integers into the column, the database rejected the value

This led to a discussion about setting up monitoring for this kind of problem in our software. We have a place for monitoring and health-checks for all our databases. We just need to know how to define them.

So how do I create a script that reports any tables whose current identity values are above a certain threshold? This is what I came up with. Maybe you’ll find it useful too.

Find Tables in SQL Server Running Out Of Identity Room

declare @percentThreshold int = 70;
 
select t.name as [table],
       c.name as [column],
       ty.name as [type],
       IDENT_CURRENT(t.name) as [identity],
       100 * IDENT_CURRENT(t.name) / 2147483647 as [percent full]
from   sys.tables t
join   sys.columns c
       on c.object_id = t.object_id
join   sys.types ty
       on ty.system_type_id = c.system_type_id
where  c.is_identity = 1
and    ty.name = 'int'
and    100 * IDENT_CURRENT(t.name) / 2147483647 > @percentThreshold
order  by t.name

Other Notes

• I really prefer sequences for this kind of thing. Monitoring goes along similar lines
• I only worry about ints. Bigints are just too big.

I was looking at the docs for DROP TABLE and I noticed this in the syntax: [ ,...n ]. I never realized that you can drop more than one table in a statement.

You Still Have to Care About Order

I think that’s great. When dropping tables one at a time. You always had to be careful about order when foreign keys were involved. Alas, you still have to care about order. The docs say:

If both the referencing table and the table that holds the primary key are being dropped in the same DROP TABLE statement, the referencing table must be listed first.

That means that when you run

CREATE TABLE A ( id INT PRIMARY KEY );
CREATE TABLE B ( id INT FOREIGN KEY REFERENCES A(id) );
 
DROP TABLE IF EXISTS A, B;

It fails with

Msg 3726, Level 16, State 1, Line 4
Could not drop object ‘A’ because it is referenced by a FOREIGN KEY constraint.

But this ordering

DROP TABLE IF EXISTS B, A;

succeeds.

I think that order shouldn’t matter here. It’s not very SQL-like. If you think so too, vote for this suggestion In DROP TABLE statement, make table order irrelevant.

DROP TABLE IF EXISTS fails

This is a suggestion made by Matt Smith. Currently DROP TABLE behaves this way:

• DROP TABLE IF EXISTS succeeds if the table exists and is deleted.
• DROP TABLE IF EXISTS succeeds when there is no object with that name.
• DROP TABLE IF EXISTS fails when that object name refers to an object that is not a table

For example, this script

CREATE VIEW C AS SELECT 1 AS One;
go
DROP TABLE IF EXISTS C;

gives the error

Msg 3705, Level 16, State 1, Line 9
Cannot use DROP TABLE with ‘C’ because ‘C’ is a view. Use DROP VIEW.

It’s really not in the spirit of what was intended with “IF EXISTS”. If you want to vote for that suggestion, it’s here DROP TABLE IF EXISTS fails

DROP TABLE is not Atomic

I’ve gotten really used to relying on atomic transactions. I know that when I update a set of rows, I can rely on the fact that all of the rows are updated, or in the case of an error, none of the rows are updated. There’s no situation where some of the rows are updated. But a DROP TABLE statement that tries to drop multiple tables using the [ ,...n ] syntax doesn’t behave that way. If there’s an error, SQL Server continues with the list dropping the tables that it can.

We can see that with the first example. Here it is again:

CREATE TABLE A ( id INT PRIMARY KEY );
CREATE TABLE B ( id INT FOREIGN KEY REFERENCES A(id) );
 
DROP TABLE IF EXISTS A, B;
-- Could not drop object 'A' because it is referenced by a FOREIGN KEY constraint.
-- B is dropped

That example throws an error and drops a table.

The same nonatomic behavior is seen in a simpler example:

CREATE TABLE D ( id INT );
 
DROP TABLE E, D;
-- Invalid object name 'E'
-- D is dropped

So check out this code, what’s going on here?

begin transaction
 
insert d1.dbo.T1 values (1);
insert d2.dbo.T1 values (1);
 
commit

The transaction is touching two different databases. So it makes sense that the two actions should be atomic and durable together using the one single transaction.

However, databases implement durability and atomicity using their own transaction log. Each transaction log takes care of its own database. So from another point of view, it makes sense that these are two separate transactions.

Which is it? Two transaction or one transaction?

It’s One Transaction (Mostly)

Microsoft’s docs are pretty clear (Thanks Mladen Prajdic for pointing me to it). Distributed Transactions (Database Engine) says:

I can actually see that happening with this demo script:

use master
if exists (select * from sys.databases where name = 'D1')
begin
    alter database D1 set single_user with rollback immediate;
    drop database D1;
end
go
 
if exists (select * from sys.databases where name = 'D2')
begin
    alter database D2 set single_user with rollback immediate;
    drop database D2;
end
go
 
create database d1;
go
 
create database d2;
go
 
create table d1.dbo.T1 (id int);
create table d2.dbo.T1 (id int);
go
 
use d1;
 
CHECKPOINT;
go
 
begin transaction
 
insert d1.dbo.T1 values (1);
insert d2.dbo.T1 values (1);
 
commit
 
select [Transaction ID], [Transaction Name], Operation, Context, [Description]
from fn_dblog(null, null);

That shows a piece of what’s going on in the transaction log like this:

If you’re familiar with fn_dblog output (or even if you’re not), notice that when a transaction touches two databases, there are extra entries in the transaction log. D1 has LOP_PREP_XACT and LOP_FORGET_XACT and D2 only has LOP_PREP_XACT. Grahaeme Ross wrote a lot more about what this means in his article Understanding Cross-Database Transactions in SQL Server

Well that’s good. I can count on that can’t I?

Except When …

You Break Atomicity On Purpose
Well, they are two databases after all. If you want to restore one database to a point in time before the transaction occurred but not the other, I’m not going to stop you.

Availability Groups
But there’s another wrench to throw in with Availability Groups. Again Microsoft’s docs are pretty clear on this (Thanks Brent for pointing me to them). In Transactions – availability groups and database mirroring they point out this kind of thing is pretty new:

In SQL Server 2016 SP1 and before, cross-database transactions within the same SQL Server instance are not supported for availability groups.

There’s support in newer versions, but the availability group had to have been created with WITH DTC_SUPPORT = PER_DB. There’s no altering the availability group after it’s been created.

It’s also interesting that availability groups’ older brother, database mirroring is absolutely not supported. Microsoft says so several times and wants you to know that if you try and you mess up, it’s on you:

… any issues arising from the improper use of distributed transactions are not supported.

Long story short:

• Cross DB Transactions in the same server are supported with Availability Groups in SQL Server 2017 and later
• Cross DB Transactions are not supported with mirrored databases at all