Compiled plans and syscacheobjects

Hi,
I'm wondering if someone knows what causes a compiled plan to get
dropped from the cache. We've got one fairly complicated procedure
that gets removed from cache every few minutes, and then compiled again
the next time its run. The compile takes 10-15 seconds of cpu. The
procedure is used 10-20 times per minute, so i dont think it's because
of lack of use. And we have other procedures called as often that dont
get dropped so frequently. As well, we have the identical procedure,
named differently (called from a different program) that shows the same
behavior, it gets dropped from the cache frequently.
Could it be related to the data the query is looking it?
Any ideas appreciated. Oh yeah, SQL Server 2000, SP3 i think.
Thanks,
Trevor
tdunsford@.gmail.com wrote:
> Hi,
> I'm wondering if someone knows what causes a compiled plan to get
> dropped from the cache. We've got one fairly complicated procedure
> that gets removed from cache every few minutes, and then compiled again
> the next time its run. The compile takes 10-15 seconds of cpu. The
> procedure is used 10-20 times per minute, so i dont think it's because
> of lack of use. And we have other procedures called as often that dont
> get dropped so frequently. As well, we have the identical procedure,
> named differently (called from a different program) that shows the same
> behavior, it gets dropped from the cache frequently.
> Could it be related to the data the query is looking it?
> Any ideas appreciated. Oh yeah, SQL Server 2000, SP3 i think.
> Thanks,
> Trevor
>
Does this procedure create temp tables?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||No, it uses table variables:
declare table @.tbl (...)
insert into @.tbl (...)
select ...
select * from @.tbl
Tracy McKibben wrote:
> tdunsford@.gmail.com wrote:
> Does this procedure create temp tables?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||tdunsford@.gmail.com wrote:
> No, it uses table variables:
> declare table @.tbl (...)
> insert into @.tbl (...)
> select ...
> select * from @.tbl
>
Have you reviewed this KB article?
http://support.microsoft.com/kb/243586
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||>From what i can understand, this talks about recompiles. I may be
mistaken, but I dont think thats what i'm seeing here. There is no
sp:recompile event. All the cpu/delay occurs before any of the
statements are executed (ie before SP:Starting).
I see 2 options, KEEP PLAN and KEEPFIXED PLAN. Would this help keep
the compile plan in the cache as well, not just the execution plan?
Because recompiling the execution plan takes no time at all...its the
compile plan thats the killer.
Thanks,
Trevor
Tracy McKibben wrote:
> tdunsford@.gmail.com wrote:
> Have you reviewed this KB article?
> http://support.microsoft.com/kb/243586
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Yes you should look into these. The execution plan is based off the compiled
plan so you can't have an executionplan without the compiled plan.
Andrew J. Kelly SQL MVP
<tdunsford@.gmail.com> wrote in message
news:1161355332.974366.216790@.e3g2000cwe.googlegro ups.com...
> mistaken, but I dont think thats what i'm seeing here. There is no
> sp:recompile event. All the cpu/delay occurs before any of the
> statements are executed (ie before SP:Starting).
> I see 2 options, KEEP PLAN and KEEPFIXED PLAN. Would this help keep
> the compile plan in the cache as well, not just the execution plan?
> Because recompiling the execution plan takes no time at all...its the
> compile plan thats the killer.
> Thanks,
> Trevor
>
> Tracy McKibben wrote:
>