Need Help !! Its urgent

Question

Need Help !! Its urgent

Zohaib Anwar

Ten Centuries

Points: 1120
More actions
January 19, 2015 at 5:00 am

#302482

exec sp_reset_connection how it can be enabled in SQL Server?
Your urgent reply is highly appreciated!!!! as our customer is having performance issues

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

sp_reset_connection is used with connection pooling, it's called when a connection is reset and goes back into the connection pool.

As for analysing your trace, have a look at the methodology in these articles, it should help you narrow down a cause:

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

For deadlocks, on SQL 2008 you can check the extended events system health session or turn traceflag 1222 on and check the deadlock graph in the SQL error log.

p.s. Performance analysis as you're asking for it (analyse trace, identify problems) is something that I'd do over 3-4 days for a client. It's not a 10 minute problem. If you're interested in getting an outside party involved to help out, drop me a PM with an email address and we can discuss rates.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

sp_reset_connection is connection pooling that closes connections and puts them back in the pool. This is totally expected and nothing you should worry about.

Analyzing a trace file for you looks more "free consulting" than "community advice". Probably someone will take the time to do so, but be aware that it's not what the forums are for.

Please, show us what you have already done.

-- Gianluca Sartori

Zohaib Anwar Ten Centuries Points: 1120 More actions · Answer 3

Thanks for the quick response!!!

What's surprising is that only this customer's trace file who is using our application is showing

exec sp_reset_connection

Our application is build up of .net using linq sql and entity frame work. Dev code is same for each and every client then why only this customer's connection is resting each time while executing SQL statement any clue for that??

Is that related to TRANSACTION ISOLATION LEVEL ???

I mean if anyone changed any server setting suppose TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED then do we get exec sp_reset_connection ??? with every SQL Statement??

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

to really understand which of the queries are giving you the most pain, I strongly suggest you run the trace file through the RML Utilities (you'll have to recapture one for this).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Zohaib Anwar Ten Centuries Points: 1120 More actions · Answer 5

Please please reply me its urgent is there any sql server side setting whom can enable this???

sp_rest_connection???

as only this customers trace file is showing such behavior..

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 6

did other customers disable connection pooling ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Zohaib Anwar Ten Centuries Points: 1120 More actions · Answer 7

I am not sure how can I check this in SQL Server ?? Is there any option / setting in SQL to enable or disable that??

If yes then please share it with me as I don't have any idea how to check it.

Thanks for your help in advance

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

Zohaib Anwar (1/19/2015)
I am not sure how can I check this in SQL Server ?? Is there any option / setting in SQL to enable or disable that??

No, it's a client-side setting.

You have to add this to your connection string:

Pooling=false;

But I highly doubt that your customers are messing with connection strings.

-- Gianluca Sartori

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 9

spaghettidba (1/19/2015)
Zohaib Anwar (1/19/2015)
I am not sure how can I check this in SQL Server ?? Is there any option / setting in SQL to enable or disable that??
No, it's a client-side setting.
You have to add this to your connection string:
Pooling=false;
But I highly doubt that your customers are messing with connection strings.

Application side setting, indeed ! ( app server or client that runs the app )

You can also disable it for a given driver in the odbc settings by disabling connection pooling for e.g. sqlserver.

( see attachment )

double click the wanted driver to be able to disable/enable it.