SQL: What is sp_reset_connection and should I care about it?
Anyone who’s ever traced activity against a SQL Server will have no doubt seen a large number of commands where the procedure sp_reset_connection has been executed. Yet, this command won’t appear anywhere in the source code of the applications that are running.
As an example of why this occurs, one of the most common data access technologies that is used to connect applications to SQL Server is ADO.NET. It has a SqlConnection object that represents a connection that can be opened to a SQL Server instance. In the design of the SqlConnection class, the architects of it were grappling with two big issues:
- They knew that opening and closing connections to SQL Server was a relatively expensive process.
- They also knew that on a busy website, they didn’t want to use enough resources (or might not even have had them), to open up a connection for each concurrent session on the website.
So they decided to make the connections to SQL Server able to be shared. By default, when you execute the Close() method of the SqlConnection, the underlying connection doesn’t get closed, it just gets added back into a shared set of connections known as the connection pool. And when the Open() method is executed, it first checks if there is an available connection in the pool and provides it, before going to create an actual connection.
By default, the pool allows for 100 shared connections.
(It’s actually a little more complex than this because it depends upon whether the connections are the same ie: shareable in the first place. As a simple example, they’d all need to use the same credentials or a separate pool is needed).
One of the challenges though, is that SQL Server has settings that can be configured at the session level. You don’t want a new Open() picking up session settings from the previous user of the connection. Sessions can also have state. You don’t want a new Open() picking up an uncommitted transaction from the previous user of the connection either.
SQL Server provides a procedure sp_reset_connection that is designed to reset the settings and the state. So when you use connection pooling (the default), every time you open a connection, ADO.NET executes sp_reset_connection to make sure you get a “clean” connection before it hands it over to you.
Is it perfect?
No but it’s pretty good. There have been versions where things like transaction isolation levels were not reset, when everything else was. (That’s fixed in current versions).
Does it incur an overhead?
Yes. A whole bunch of code gets executed internally when this is run and obviously that’s an overhead. But nowadays, the bigger concern is the latency for the round trip over the network. Back when your DB server might have been on the same system, this wasn’t so much of an issue, but when your DB server might be somewhere in the cloud, over a potentially higher latency connection, this is more of an issue.
Can I avoid it?
Sure. You can set Pooling=False as an option in your connection string. It’s also possible to change the pool size there too.
Should I avoid it?
That’s a tough question. In general, for most web apps, the simple answer is no. There’s a good reason why it was added. Opening and closing actual connections is still a relatively expensive pair of operations. But there are other types of applications where there really isn’t a need for a connection pool and its associated overhead.
If you are performing tracing on SQL Server workloads, chances are high that you won’t have control over that, and you’ll probably want to just ignore executions of sp_reset_connection.
In the LoadPerformanceTuningTrace procedure in our free SDU Tools for DBAs and Developers, we have a parameter @IgnoreSPReset that does just that.
2018-07-30