my SP slows after 1 minute

Hello
I have a large query that takes 2:05 secs to run 1st time. If I rerun the
query within a minute or so, the subsequent runs take only :30 secs. If I
wait over a minute, it reruns at 2:05 again.
I have run the Query Tuning Wiz on this code and it recommended (and I
implemented) some addl indexes. Not much improvement here, only a few secs.
The machine has 4GB Mem and SQL Server is using well over 3. Additionally, I
have run SP_RECOMPILE against my SP but no addtl improvement on new runs.
Can someone give me some tips on where to go from here? Is the data being
paged back out? The cache hit ratio is >90%
Jim
BostonHi JimMC,
Probably after a minute, the procedure is getting flushed out from the cache
?
Step 1:
(a) Run the SP and note the duration
(b) Run the SP again immediately and note the duration. It should be the
same duration as in (a), as mentioned by you.
(c) Issue DBCC FREEPROCCACHE.
(d) Run the SP and note the duration
Execute all the above within a minute. If (d) is taking more time, it means
that the SP is getting flused out of cache after a minute, in this case.
Am guessing that one of the other reason could be data itself being flushed
out.
Thanks
Yogish|||I will be very reluctant to run DBCC FREEPROCACHE on a production server as
this temporarily degrades the performance of all stored procedures server
wide.
I will however perform the following
- check the execution plan of the query - when it runs fast
switch on the following
SET STATISTICS IO
SET STATISTICS PROFILE
SET SHOWPLAN_ALL
and establish if the correct indexes are being used during the execution
- also U need to find out (through STATISTICS io) to find out if physical
reads are higher (indicating that buffercache might have been flushed) durin
g
when it runs slow in the space of 1minute.
- I also monitor buffer cache hit ratio and find out what the percentage is.
ie. what percentage of data is found in buffer cache?)
HTH
"Yogish" wrote:

> Hi JimMC,
> Probably after a minute, the procedure is getting flushed out from the cac
he?
> Step 1:
> (a) Run the SP and note the duration
> (b) Run the SP again immediately and note the duration. It should be the
> same duration as in (a), as mentioned by you.
> (c) Issue DBCC FREEPROCCACHE.
> (d) Run the SP and note the duration
> Execute all the above within a minute. If (d) is taking more time, it mean
s
> that the SP is getting flused out of cache after a minute, in this case.
> Am guessing that one of the other reason could be data itself being flushe
d
> out.
> --
> Thanks
> Yogish
>|||Maybe Optimizing Query's in the SP will be a better Idea.
It seems that therer not so much thngs to do with it.
"JimMc" <JimMc@.discussions.microsoft.com> wrote in message
news:35D535B9-5DDA-4C25-BE59-691D1E6B93C2@.microsoft.com...
> Hello
> I have a large query that takes 2:05 secs to run 1st time. If I rerun the
> query within a minute or so, the subsequent runs take only :30 secs. If I
> wait over a minute, it reruns at 2:05 again.
> I have run the Query Tuning Wiz on this code and it recommended (and I
> implemented) some addl indexes. Not much improvement here, only a few
secs.
> The machine has 4GB Mem and SQL Server is using well over 3. Additionally,
I
> have run SP_RECOMPILE against my SP but no addtl improvement on new runs.
> Can someone give me some tips on where to go from here? Is the data being
> paged back out? The cache hit ratio is >90%
> Jim
> Boston|||Thank you ALL!
your suggestions and experience helped. The issue actually cured itself.
After I did the SP_RECOMPILE then after an additional 10 min I tried it agai
n
and it's ET was reduced to 9 seconds vs the initial 30. I think that when I
ran it the first time after the SP_RECOMPILE I still was provided a stale
copy of the SP. But after waiting the addl time SQL Server seems to have
given me the newer copy.
Thanks again Very Much!
Jim
"Olu Adedeji" wrote:
[vbcol=seagreen]
> I will be very reluctant to run DBCC FREEPROCACHE on a production server a
s
> this temporarily degrades the performance of all stored procedures server
> wide.
> I will however perform the following
>
> - check the execution plan of the query - when it runs fast
> switch on the following
> SET STATISTICS IO
> SET STATISTICS PROFILE
> SET SHOWPLAN_ALL
> and establish if the correct indexes are being used during the execution
> - also U need to find out (through STATISTICS io) to find out if physical
> reads are higher (indicating that buffercache might have been flushed) dur
ing
> when it runs slow in the space of 1minute.
> - I also monitor buffer cache hit ratio and find out what the percentage
is.
> ie. what percentage of data is found in buffer cache?)
>
> HTH
>
>
>
> "Yogish" wrote:
>