odd server behavior

Hello everyone,
One of our applications was timing out while executing a certain
stored procedure that inserts information in a group of tables.
When I checked the situation via Profiler, that particular SP has
indeed an almost consistent duration of roughly 30 seconds (which was
the timeout setting at the frontend).
I copied one of the textdata entry captured in Profiler and executed
this in QA. This executed for almost 2 minutes after which all
subsequent executions of this stored procedure via the application
returned to normal with a duration of about 5 to 10ms.
Has anyone had a similar experience? I am at a loss at what happened,
and I don't know where to begin looking for the cause as it has
resolved itself when I was just attempting to look into it.
Thanks.
AramidIt could be that the auto update stats kicked in, or that the data was
readily available in cache. Do check in Profiler to see if there are any
recompilations. Also capture the execution plan in Profiler, and compare
with the one you are getting in Query Analyzer. It would also help if you
had posted the stored procedure code, so that we can point out any issues.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"aramid" <aramid@.hotmail.com> wrote in message
news:v5s371p46il11k82jn9tene1701fmtad2j@.
4ax.com...
Hello everyone,
One of our applications was timing out while executing a certain
stored procedure that inserts information in a group of tables.
When I checked the situation via Profiler, that particular SP has
indeed an almost consistent duration of roughly 30 seconds (which was
the timeout setting at the frontend).
I copied one of the textdata entry captured in Profiler and executed
this in QA. This executed for almost 2 minutes after which all
subsequent executions of this stored procedure via the application
returned to normal with a duration of about 5 to 10ms.
Has anyone had a similar experience? I am at a loss at what happened,
and I don't know where to begin looking for the cause as it has
resolved itself when I was just attempting to look into it.
Thanks.
Aramidsql