3

Theres a lot of questions here on DBA EXCHANGE. But none of them can help me.

I have 2 problems:

1) Using sp_whoisactive, I found lots of sys.sp_reset_connection;1( I know they are sp_reset_connections because I used dbcc inputbuffer(SPID):

Lots of resets

This is the "main server", so the other servers ( 3,4,7 ) are constantly requesting information of some databases here ( And what I see is, every request is for the MASTER database ).

Databases


2) Why they're taking so much time?

Looking at some answers here, I found that the system is waiting for the CPU. but all CPU is null. How can this be the problem?

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.

Good...But I still don't know what to do.

This is not giving me problems. I just want to know how to troubleshoot this.

3

sp_reset_connection is an artifact of using Connection Pooling. And what you think you are seeing is not what is actually happening.

Connection Pooling is a feature of the client library more than of SQL Server. The client provides most of the functionality, while SQL Server itself mainly provides sp_reset_connection. When using Connection Pooling, the client driver does not actually close the physical connection to SQL Server when the app code calls the "close connection" method. Instead, the driver keeps the connection open and will link it to the next "open connection" request that uses the exact same connection string (and from the same Windows account, if using a Trusted Connection / Integrated Security). Now, because the connection was never closed, SQL Server knows only that the client is keeping the connection open, but not why. And because the connection is remaining open, the session still exists with whatever resources and settings were in place as of the last command / batch executed. This is why temporary tables created in the main scope (i.e. not in a stored procedure) will continue to exist (for details, please see: Sessions, Temporary Objects, and the Afterlife), and open transaction will stay open.

SQL Server doesn't know that connection pooling is being used until the next command / batch is submitted, at which point it executes sp_reset_connection. It is sp_reset_connection that cleans up the prior state of the session so that it will be just like a brand new session, which is what the client code is expecting (with a few minor items that do not get cleaned up / reset).

Of course, that description is based on the newer drivers. As the quote in the question states, older drivers sent a separate request for sp_reset_connection. The fact that you are seeing sessions that have no currently executing request (i.e. CPU is NULL) is expected when using Connection Pooling. What is not typical is seeing sp_reset_connection via DBCC INPUTBUFFER. This is not typical since the newer drivers request sp_reset_connection upon submitting the first batch / command upon reconnecting, so you would never see sp_reset_connection since it will never be the last thing to execute (though you can see it using SQL Server Profiler or Extended Events). But, if you are seeing it using DBCC INPUTBUFFER (which will continue to show the last command / batch executed, even after the batch completes), then that would appear to match the behavior of the older drivers (assuming that the older drives, because they are not sending a flag along with the next command / batch, are requesting sp_reset_connection upon "close connection" requests).

Either way, sp_reset_connection is not currently running. And the reason that the requests are all for the master DB is most likely do to master being the default Database for the Login(s) being used.

2

If there are no performance issues, you can probably ignore what you are seeing. See sp_reset_connection – Rate Usage (Don’t fight over the grapes) by Bob Dorr - Principal SQL Server Escalation Engineer, highlights below:

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.

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.

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


Dan Guzman also commented:

sp_reset_connection typically takes no more than a couple of milliseconds (usually microseconds). The exception is when a rollback is needed because the connection was returned to the pool with an open transaction. I suspect when you run DBCC INPUTBUFFER, then it is the last command that was run, not the one that was taking a long time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.