Re: compilation and execution plan

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 11/26/04 

Date: Fri, 26 Nov 2004 12:22:27 -0800

Not quite.
The explanation of the terms is fine, but this is not how Profiler records
them.

If you actually look at what happens when you issue the sp_recompile, you'll
see a SP:CacheRemove event. Then the next time you call the proc, you'll see
SP:CacheInsert just as for the first time.

AFAIK, the SP:Recompile event is ONLY generated in the situation where a
stored procedure is recompiled while it is executing. This is caused by
activities in the sproc like DDL, updating statistics or changing a SET
option.

See KB 308737 for more details. 

-- 
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"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: 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: 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: compilation and execution plan
    ... create the proc, the proc plan doesn't exist and fir the first execution you get a compilation. ... However, if you sp_recompile a table that the proc is using, the plan is still in cache and for the ...
    (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)
  • compilation and execution plan
    ... have SP which plan is already in cache. ... exec MySP - sp:cachemiss, sp:cacheinsert ... when I execute SP after DBCC and then after DROP/CREATE statements? ... My understanding is that if plan is removed from cache ...
    (microsoft.public.sqlserver.server)
Loading