Re: compilation and execution plan

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Alex (alex_removethis__at_healthmetrx.com)
Date: 11/25/04 

Date: Thu, 25 Nov 2004 08:09:57 -0800

Thank you Tibor,

So Profiler has event related to recompilation (sp:recompile) and doesn't
have event related to compilation, that's why we see only sp:cachemiss,
sp:cacheinsert and nothing in between?

Thank you,

Alex

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:%23Wuoqeu0EHA.1392@TK2MSFTNGP14.phx.gbl...
> Alex,
>
> The difference is in the difference between the words "compile" and
"recompile". If you drop and
> create the proc, the proc plan doesn't exist and fir the first execution
you get a compilation. Same
> if you empty the proc cache.
>
> However, if you sp_recompile a table that the proc is using, the plan is
still in cache and for the
> next execution SQL Server will need to REcompile that plan.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> "Alex" <alex_remove_this_@telus.net> wrote in message
news:pSepd.2111$cE3.1783@clgrps12...
> > Hi guys,
> >
> > I'm confused on following situation, - I'm definitely missing something.
I
> > have SP which plan is already in cache. The SP references table MyTable.
I
> > setup trace on recompile and all cache related events and it runs during
> > test nonstop.
> >
> > Here is what I did (before dash) and
> > what I saw in Profiler (after dash) on each action:
> >
> > DBCC FREEPROCCACHE - sp:cacheremove
> > exec MySP - sp:cachemiss, sp:cacheinsert
> > drop procedure MySP - sp:cacheremove
> > CREATE PROCEDURE MySP - none
> > exec MySP - sp:cachemiss, sp:cacheinsert
> > exec sp_recompile MyTable - none
> > exec MySP - sp:ExecContextHit, sp:cacheremove,
> > sp:recompile,
> > sp:cachemiss, sp:cacheinsert
> >
> > My question is Why I can't see sp:recompile events
> > when I execute SP after DBCC and then after DROP/CREATE statements?
> > My understanding is that if plan is removed from cache
> > it doesn't exist anymore and has to be compiled to enable SP to execute
next
> > time (what we see when SP is executed after
> > exec sp_recompile MyTable)?
> > But instead we see sp:cacheinsert event right after sp:cachemiss event.
It's
> > inserted into cache but from where? On which step did it get compiled?
> >
> > I'd be highly grateful for any information.
> >
> > Alex
> >
> >
>
>

Relevant Pages

  • Re: compilation and execution plan
    ... > have event related to compilation, that's why we see only sp:cachemiss, ... the proc plan doesn't exist and fir the first execution ... >> if you empty the proc cache. ... >> next execution SQL Server will need to REcompile that plan. ...
    (microsoft.public.sqlserver.server)
  • Re: Periodic spike on stored procedure execution time
    ... There's no cache remove within the procedure. ... Among the 200 execution, the recompile of plan takes a consistent ...
    (microsoft.public.sqlserver.server)
  • Re: What exactly is a query plan (or a SQL plan for that matter)
    ... If it had to recompile the plan each time the query was run and you ... ran this query several tims a second you would be losing significant CPU ... > cache these queries? ...
    (microsoft.public.sqlserver.programming)
  • Re: gfortran, g95, and dual-core
    ... either speed of compilation or speed of execution? ... Many, many things matter. ... That's why cache is ... Is this apt to make a big difference (factor of 2 in execution speed) when comparing different Intel-compatible chips in a given price range? ...
    (comp.lang.fortran)
  • Re: Lisp2Perl - Lisp to perl compiler
    ... At some point, you decide to recompile from scratch, ... > recompile the utility file that expanded the macro, ... > world compilation are treated more uniformly. ... Tyler: "How's that working out for you?" ...
    (comp.lang.lisp)