sp_reset_connection – Rate Usage (Don’t fight over the grapes)
Discussions surrounding sp_reset_connection behavior and usage come up all the time and over the years I have discussed the subject 100s of times with customers. Blogs, API documentation and Books Online describe SQL Server, pooled connections behavior.
Under the covers SQL Server uses the sp_reset_connection logic to ‘reset’ the connection state for the SQL Server, which is faster than establishing a completely new connection. Older drivers send the procedure call as a separate, TDS, round-trip to the SQL Server. Newer client drivers add a flag bit along with the next command, avoiding the extra network round-trip.
The discussions quickly turn to: “What is a reasonable rate of sp_reset_connections on my server?” As usual the answer is always it depends.
However, a cursory inspection of the rate usually reveals itself as I am not that worried or my stomach hurts. – The simple, old, smell test works pretty well for getting a high level understanding of your system. Then you can work with your application developers to tune the behavior accordingly.
The documentation refers to the concepts of Open Late and Close Early. Meaning you open the connection right before you need it and you release the connection as soon as it is no longer needed. This allows the connection pool to work optimally by sharing the connection whenever possible.
The problem I most often see is that this behavior is taken to the extreme. The development team often cookie cutters functional logic.
MyFunction
{
Get Connection
Run Query
Release Connection
}
The function uses Open Late / Close Early just like the documentation pointed out. Now imagine you have dozens or 100s of these functions in the application. The problem is that seldom does logical business activity call a single function activity.
LoadMyPage
{
Call Func1
Call Func2
Call Func3
}
In this example the application drives the connection pool 3 times, resulting in 3, sp_reset_connection operations. This is the worst case scenario with a 1:1 ratio of commands to sp_reset_connection invocations.
| Don’t fight over the grapes You may have wondered why this was in the title. It is because I was recently sitting in an airport and two little girls gave me an analogy for sp_reset_connection. They looked to be about 3 or 4 years old and were sharing a bag of grapes. They started out very polite, each taking just one grape at a time. However, they kept waiting on each other and as time went on they got a bit more combative until the mother finally told them not to fight over the grapes. Instead of taking one at a time take a handful. Then they would not be constrained all the time waiting to get access to the bag of grapes. If you will, she optimized their activity. |
The grapes analogy was perfect. If each command in the application acquires a connection, executes and releases you are placing pressure and resource constraints on the connection pool. Each time the sp_reset_connection executes it uses resources on the SQL Server and client.
It is far better to write the application logic to avoid contention points, and align with logical units of work. This maintains the concepts of Open Late and Close Early while reducing overhead and improving performance.
LoadMyPage
{
conn = Get Connection
Call Func1(conn)
Call Func2(conn)
Call Func3(conn)
Release conn
}
In this example the connection spans the logic to load the page. The connection to command ratio goes from 1:1 to 1:3 and in doing so removes 2 of the sp_reset_connection activities.
Simply put you need to find a healthy sp_reset_connection to command ratio for your environment. I can tell you that 1:1 is poor and the applications that I see functioning well are usually in the 1:8, 1:10, 1:15 range.
There is not a hard and fast rule but using performance monitor you can quickly compare the overall batch rate to the reset connection rates. When I see the rate start to climb above 15% it is a pretty good indication that the application may need to be revisited and tuned a bit.
It is true that the connection reset may not be driving your CPU load (sp_reset_connection has been tuned and is lightweight in general) as compared to the overall work done by the TSQL commands executing on the SQL Server. You should think of this more as a gate than a CPU consumer. Before the command, that is doing the work you need it to do can execute, the reset must complete. While the delay is small in wall-clock time the overall performance of the application could be better with strategic use of the reset behavior.
The load page examples above will simply run faster with the 1:3 ratio because it avoids the 2 trips though the pooled connection logic.
With all this said, you need to be careful that you don’t extend the ratio too far. Keeping the connection when it is not needed will increase the overall number of connections using more client and SQL Server overhead. You need to find the sweet spot that optimizes the client and SQL Server resources and maximizes the application performance capabilities.
You may also consider recent changes that reduce the overhead of sp_reset_connection at the SQL Server https://support.microsoft.com/kb/2926217
The following are additional references pertaining to the sp_reset_connection subject https://blogs.msdn.com/b/psssql/archive/2010/12/29/how-it-works-error-18056-the-client-was-unable-to-reuse-a-session-part-2.aspx https://blogs.msdn.com/b/psssql/archive/2013/02/13/breaking-down-18065.aspx https://support.microsoft.com/kb/180775
Bob Dorr - Principal SQL Server Escalation Engineer
Comments
Anonymous
March 03, 2014
Bob, The KB2926217 is a hotfix for SQL2012. It also mentions SQL2008R2. Will it be included in the enxt set of cumulative updates for both versions? Thanks ChrisAnonymous
April 04, 2014
Plans to back port this change to R2 are not finalized yet.Anonymous
November 04, 2015
Hi Bob, do you know if sp_reset_connection also sets back the deadlock priority to normal? Thanks! Lukas