too many exec sp_reset_connection

Question

Post reply

too many exec sp_reset_connection

Pyman

SSChampion

Points: 13830
More actions
March 21, 2012 at 10:20 am

#255168

I have been informed by the dev team that they are noticing timeout issues and sql exceptions in the application server end. I wanted to know what was happening and ran the profiler and to my surprise I am seeing too many exec sp_reset_connections for the event class RPC:Completed. Per my research SP_reset_execution aborts any open transactions; if this is the case then its definitely something we dont want to happen. Is there a way that I can reduce the number of connection resets? Please suggest.
Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jared SSC Guru Points: 61793 More actions · Answer 1

How do you know it is "too many?" What exactly are you basing that on?

Jared
CE - Microsoft

Pyman SSChampion Points: 13830 More actions · Answer 2

Thats based on the count (number of times it appears in the trace file)

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Jared SSC Guru Points: 61793 More actions · Answer 3

And that number is 10/minute? 20/minute? 2000/minute? How are you judging that it is too many? Every time a connection is reusing a pool, it is executing this. So what I am asking is... How do you know that it is too many? Not how do you know what the count is... What makes it too many versus just enough, or too little?

Jared
CE - Microsoft

Pyman SSChampion Points: 13830 More actions · Answer 4

And that number is 10/minute? 20/minute? 2000/minute? How are you judging that it is too many? Every time a connection is reusing a pool, it is executing this. So what I am asking is... How do you know that it is too many? Not how do you know what the count is... What makes it too many versus just enough, or too little?
Thanks

I ran the profiler for 5 mins and I saw the sp_reset_connection almost 1300/minute. So when a connection is being reused does that mean any active connections are being aborted after a specific time interval (based on a connection pool time in a connection string?) to be reused?

Thanks Jared

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Lowell SSC Guru Points: 323518 More actions · Answer 5

does your application make hundreds of separate trips to the server? so for example if you have a dataset with 100 table,s it makes 100 separate calls to the server to load those tables?

or is it smart enough to make ONE connection, query a bunch of stuff, and then disconnect?

I've also seen applications where a trip to the server is used to get a description for a single field, like the cityname for citykey, and stuff which could also be why you see lots of sp_reset_connection.

however, none of those things mean you have "too many".

sp_reset_connection does nto have anythign to do with the other complaint you mentioned...applicaitons timing out.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jared SSC Guru Points: 61793 More actions · Answer 6

Jared

SSC Guru

Points: 61793

March 21, 2012 at 3:11 pm

#1462447

Check out this article.

Jared
CE - Microsoft

Lowell SSC Guru Points: 323518 More actions · Answer 7

if you want to start digging into slow queries, something like this will get you started.

you could then start reviewing the querie plans and see where the perforance issues are...non-SARGABLE queries, missing indexes, out of date statistics, etc.

SELECT fn.*,

st.*

FROM sys.dm_exec_query_stats st

CROSS apply sys.DM_EXEC_SQL_TEXT(st.[sql_handle]) fn

WHERE st.max_elapsed_time >= ( 1000 * 15 ) --15 seconds

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!