December 26, 2005 at 9:27 pm
Dear all,
One of our client have a Vb.Net aplication which is accessed by many users remotly.
They have performance issues and on running trace through Sql Profilor we captured some events and one of the event is sp_resert_connection which is executed every 5 sec.
I need to know how to stop this execution off of sp_reset_connection and what is the reason it is executed so often.
Thanx for ur time.
from
Killer
December 27, 2005 at 12:50 am
Your application is using connection pooling, which isn't necessarily a bad thing (is usually good!).
Effectively ADO.NET will keep a connection open to your database for subsequent application "connections" to reuse, rather than going through the expensive process of opening and closing a database connection for each query you execute. To ensure that the connection that is kept open is reset back to a well-defined state (eg no transaction, SET statements, etc), the sp_reset_connection proc is executed by ADO.NET.
I suspect you may wish to look elsewhere for your slow performance. Using profiler, see if any of your queries are taking longer than normal to execute. Do you have >5 connections to your DB? If so, are you running MSDE/SQL Express?
December 28, 2005 at 2:17 am
HI Thanx for the reply,
The sp_reset_connection is called 3,898 times in 8 hours.Is this ok i mean is it not bad.That the stored procedure is called upon so many times and every 5 sec.
We have SQL Standered Edition supporting 2 GB of RAM Database size in 2 GB.
There are few Stored procedure which are executed very often.
I run the SQL Profilor and captured some events but there is no text in the textdata field how to capture that text in the profilor.
Is there any way to see the event that are not showed in profilor.
from
Killer
December 28, 2005 at 11:47 am
Ian,
When you say "performance issues" do you mean locking?
December 28, 2005 at 7:38 pm
Hi KP,
There are normal locks on the table.
from
sufian
December 28, 2005 at 8:07 pm
Not to blame the developers, but if they are writing possibly suboptimal SQL statements directly into code as opposed to using stored procedures, you may miss the statements if only monitoring sp's (and their SQL statements may be gnarling things up). What event types are you monitoring in SQL Profiler?
December 28, 2005 at 9:30 pm
For the events you do capture in profiler, try running them yourself in query analyser and see how fast / slow they are... Use the default profiler setup - it should catch most things such as adhoc statements and stored proc executions from your app.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply