Re: compilation and execution plan

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 11/25/04 

Date: Thu, 25 Nov 2004 13:09:48 +0100

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: Periodic spike on stored procedure execution time
    ... Sorry, I meant to say regenerate the query plan, which is due to the ... 4GB on test server & 32GB at production server. ... There's no cache remove within the procedure. ... Among the 200 execution, the recompile of plan takes a consistent ...
    (microsoft.public.sqlserver.server)
  • Re: compilation and execution plan
    ... 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. ... >> exec MySP - sp:cachemiss, ...
    (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: 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 ... In the trace, among the 200 execution, there're 5 ...
    (microsoft.public.sqlserver.server)
  • Re: how to check query plan without running it
    ... For example, if the proc creates a temp table, and then manipulates that table, there is no way to come up with a plan for table that hasn't been created yet. ... I'm not saying there is no point in looking at the plan without execution, but you should just be aware there are no guarantees. ... at solutions for those stored procedures which updates the table, ...
    (microsoft.public.sqlserver.server)
Loading