compilation and execution plan

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.
AlexAlex,
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
>|||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
> >
> >
>|||Yep. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex" <alex_removethis_@.healthmetrx.com> wrote in message
news:10qc0uncauftpd9@.corp.supernews.com...
> 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
> > >
> > >
> >
> >
>|||On Thu, 25 Nov 2004 06:22:13 GMT, "Alex" <alex_remove_this_@.telus.net>
wrote:
>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.
sp_recompile only marks the SP, it doesn't actually *do* the
compilation at that time, SQLServer "lazily" waits for the next call
for execution, and then compiles it.
J.|||JXStern wrote:
> On Thu, 25 Nov 2004 06:22:13 GMT, "Alex" <alex_remove_this_@.telus.net>
> wrote:
> >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.
> sp_recompile only marks the SP, it doesn't actually *do* the
> compilation at that time, SQLServer "lazily" waits for the next call
> for execution, and then compiles it.
> J.
This "laziness" avoids unnecessary compilations.
However, there is also a technical reason that SP's are not recompiled
immediate after a schema change or sp_recompile. This is because the
compilation phase uses the stored procedure parameters so the most
representative statistics can be used, and thus will not 'guess'
parameter values. This basically means that SP's cannot be compiled
without calling them (with the appropriate parameters).
Gert-Jan|||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
>>
>|||Thank you guys for all that information
Alex
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41A77B97.DA602FD7@.toomuchspamalready.nl...
> JXStern wrote:
> >
> > On Thu, 25 Nov 2004 06:22:13 GMT, "Alex" <alex_remove_this_@.telus.net>
> > wrote:
> > >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.
> >
> > sp_recompile only marks the SP, it doesn't actually *do* the
> > compilation at that time, SQLServer "lazily" waits for the next call
> > for execution, and then compiles it.
> >
> > J.
> This "laziness" avoids unnecessary compilations.
> However, there is also a technical reason that SP's are not recompiled
> immediate after a schema change or sp_recompile. This is because the
> compilation phase uses the stored procedure parameters so the most
> representative statistics can be used, and thus will not 'guess'
> parameter values. This basically means that SP's cannot be compiled
> without calling them (with the appropriate parameters).
> Gert-Jan