SEO

#1 (**permalink**) 04-08-2008, 06:18 PM

ASE is dropping plan for my procedure too frequently. This causes
procedure to take up more than double the time required to run. how can
i force server to NOT drop the cached plan??

Please help.

SS

#2 (**permalink**) 04-08-2008, 06:18 PM

SSawhney wrote:
> ASE is dropping plan for my procedure too frequently. This causes
> procedure to take up more than double the time required to run. how can
> i force server to NOT drop the cached plan??
>
> Please help.
>
> SS
>

what about increasing the procedure cache?

Try to run:
sp_monitorconfig 'all'
and check if you need more memory for the procedure cache.

#3 (**permalink**) 04-08-2008, 06:18 PM

As the previous poster said, the problem may be that your procedure
cache is too small.

Another option:

While it won't stop ASE from aging the plan from procedure cache,
you can use the Abstract Plans feature to minimize the time it takes
to generate a new plan (as the optimizer will be bypassed).

Cheers,
-bret

SSawhney wrote:
> ASE is dropping plan for my procedure too frequently. This causes
> procedure to take up more than double the time required to run. how can
> i force server to NOT drop the cached plan??
>
> Please help.
>
> SS

#4 (**permalink**) 04-08-2008, 06:18 PM

This helps. Thanks for your suggestions.

regards

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

how to avoid procedure recompile