Preventable MARS Connection Leaks
I recently looked at a SQL Server instance that had a large number of MARS connections under a single “parent” connection. Most of these “child” connections had been idle for quite a while, but they were still hanging around.
Background - Reading Data Normally
First, some context.
Normally, when you connect to SQL Server using the .NET SqlClient driver, you can only have one command running against that connection at a time.
Imagine I need 10 users and 10 posts from the Stack Overflow database. I can run those queries sequentially like this:
var connectionString = @"Server=.\SQL2019;Database=StackOverflow201812;Trusted_Connection=True;Application Name=MARS Test";
var connection = new SqlConnection(connectionString);
connection.Open();
using (var command1 = new SqlCommand("SELECT TOP (10) Id FROM dbo.Users", connection))
using (var command2 = new SqlCommand("SELECT TOP (10) Id FROM dbo.Posts", connection))
{
var reader1 = command1.ExecuteReader();
while (reader1.Read())
{
Console.WriteLine($"Found User Id {reader1.GetInt32(0)}");
}
reader1.Close();
var reader2 = command2.ExecuteReader();
while (reader2.Read())
{
Console.WriteLine($"Found Post Id {reader2.GetInt32(0)}");
}
reader2.Close();
}
But what if I want to run both queries at once? I could try to send both queries to the server, and then read their results sequentially, like this:
var reader1 = command1.ExecuteReader();
var reader2 = command2.ExecuteReader();
while (reader1.Read())
{
Console.WriteLine($"Found User Id {reader1.GetInt32(0)}");
}
while (reader2.Read())
{
Console.WriteLine($"Found Post Id {reader2.GetInt32(0)}");
}
However, that code will throw an exception when I try to run the second query (command2.ExecuteReader()
):
There is already an open DataReader associated with this Command which must be closed first.
MARS Has Entered The Chat
A “quick fix” for this issue would be to enable the “multiple active result sets” feature via this connection string option:
MultipleActiveResultSets=True
Changing that connection string makes the error above magically go away!
When I run the first code sample (with sequential queries), this is what I see on the server in terms of connections:
SELECT
c.connect_time,
c.connection_id,
c.parent_connection_id,
s.[program_name]
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s
ON s.session_id = c.session_id
WHERE
s.[program_name] = 'MARS Test'
ORDER BY c.connect_time;
There’s just one connection.
When I run the MARS-enabled code sample (with overlapping queries), the same DMV query shows this:
Now we have four connections. Two connections are established right away (the parent, and the initial child).
The other two get created as each ExecuteReader()
call is run.
How Do We Get Rid Of Them?
So what might cause these “MARS connections” to accumulate and not get cleaned up?
You might have noticed that in my code sample with the overlapping queries, I read all the results from the SqlDataReader
objects, but I never closed them.
Without MARS enabled, you have the helpful error message to remind you to close / dispose the readers.
With MARS enabled, the SqlClient driver will happily let you keep opening more and more readers, without ever closing them.
The app where I saw this issue is using kind of an unusual coding pattern, in that it maintains a single SqlConnection
object and reuses that throughout the application. This wouldn’t be a problem normally (although it’s more efficient to simply rely on the built-in connection pooling mechanism), but this app also had MARS enabled, and wasn’t calling Close()
or Dispose()
on the readers being opened.
The situation gets gradually worse and worse as more readers are opened, and abandoned, without being cleaned up:
The connections do get cleaned up if you call Close()
or Dispose()
on the readers.
Child connections created by using the ExecuteScalar()
or ExecuteNonQuery()
APIs appear to get cleaned up on their own.
Still, a better solution is to allow the SqlConnection
to be reset and returned to the pool periodically.
Consequences
As some readers may know, SQL Server allows a maximum of 32,767 connections. If I let my little test app run in a loop until I have some between 32,767 and 34,000 connections, I eventually get this error message and all the connections are dumped by the driver:
System.Data.SqlClient.SqlException: ‘A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)’ at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync() at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket() at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer() at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at MarsTest.Program.Main(String[] args) in C:\Code\MarsTest\MarsTest\Program.cs:line 25
Incidentally, not properly cleaning up the reader objects is also causing a memory leak in the .NET application:
Conclusions
It’s always best to call Dispose()
on objects that implement IDisposable
, and that includes SqlDataReader
, SqlCommand
, and SqlConnection
.
Be especially careful to do so when using the MARS feature.
Also, generally speaking, the built-in connection pooling built into SqlClient is your friend. Don’t try to build your own connection pooling.