Complete Execution History of a report

Hi,

I have some reports(both parameterized and non-parameterized) for which I need to have an execution history on the report server. These are not scheduled and so the history should only be for the instances when users view the report.

I checked the 'Store all report execution snapshots in history' but it does not store the snapshot in history everytime the report is viewed. What do I do to achieve this?

Thanks

One way is to query the ExecutionLog table in the Reporting Services Database. You will need to join to the Catalog table to retrieve the report names. The only issue here is that the ExectutionLog table is cleaned out periodically (monthly?) so you will only get the last month's worth of executions.

I have copies of the ExecutionLog and Catalog table created in another database and run a job to update them every few minutes. I run a Reporting Statisitcs report off these tables.

regards

Ray

|||

You may also want to read this RS 2005 technical article: http://msdn.microsoft.com/library/en-us/dnsql90/html/RSMnRptExPf.asp

-- Robert

|||

Hi Robert,

Thanks for your the link. However, I do not need the detailed execution statistics provided by the solution. I simply want the report snapshot to be stored in history whenever a report is viewed/run. This behaviour is by default in Crystal Enterprise 10 and I thought it would be reasonably simple in Reporting Services as well. Is the answer no ?

Thanks again,

Hemil.

|||SSRS supports automatically creating history snapshots for reports which are set to execute from a snapshot. Each time the snapshot is updated, it will be added to report history. That said, we do not support automatically creating history for every single live execution. I am not certain how useful that would be...|||

Considering this sceranio...there are three users of the same 'roles' who can access the same report. At any time whoever generates the report the first time. It will be kept in the history folder. When the rest of the users checks on the history they know who's has generated the report and that they themselve need not generate the reports ( since someone has done). I guess in this situation...it would be useful to describe why he needs that facility?

Regards

Alu

Complete Execution History of a report

Hi,

I have some reports(both parameterized and non-parameterized) for which I need to have an execution history on the report server. These are not scheduled and so the history should only be for the instances when users view the report.

I checked the 'Store all report execution snapshots in history' but it does not store the snapshot in history everytime the report is viewed. What do I do to achieve this?

Thanks

One way is to query the ExecutionLog table in the Reporting Services Database. You will need to join to the Catalog table to retrieve the report names. The only issue here is that the ExectutionLog table is cleaned out periodically (monthly?) so you will only get the last month's worth of executions.

I have copies of the ExecutionLog and Catalog table created in another database and run a job to update them every few minutes. I run a Reporting Statisitcs report off these tables.

regards

Ray

|||

You may also want to read this RS 2005 technical article: http://msdn.microsoft.com/library/en-us/dnsql90/html/RSMnRptExPf.asp

-- Robert

|||

Hi Robert,

Thanks for your the link. However, I do not need the detailed execution statistics provided by the solution. I simply want the report snapshot to be stored in history whenever a report is viewed/run. This behaviour is by default in Crystal Enterprise 10 and I thought it would be reasonably simple in Reporting Services as well. Is the answer no ?

Thanks again,

Hemil.

|||SSRS supports automatically creating history snapshots for reports which are set to execute from a snapshot. Each time the snapshot is updated, it will be added to report history. That said, we do not support automatically creating history for every single live execution. I am not certain how useful that would be...|||

Considering this sceranio...there are three users of the same 'roles' who can access the same report. At any time whoever generates the report the first time. It will be kept in the history folder. When the rest of the users checks on the history they know who's has generated the report and that they themselve need not generate the reports ( since someone has done). I guess in this situation...it would be useful to describe why he needs that facility?

Regards

Alu

Compilation of Stored Procs

Hi,

I would like to know if the execution plans of stored procs also get migrated when we do migration to 2005 from 2000 using attach\detach method or we will need to re-run the stored procs?

The thing is when I am running the Stored procs in 2005, its performing really slow in first run.

Any help in his regard is highly appreciated.

Thanks,
Ritesh

yes, dropping, removing of anything will most likely remove the cached plans for that object. The first run can be really slow, this is expected and by design, additional run should use the cached plan.

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

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.
Alex
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 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...[vbcol=seagreen]
I[vbcol=seagreen]
I[vbcol=seagreen]
next[vbcol=seagreen]
It's
>
|||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...
> "recompile". If you drop and
> you get a compilation. Same
> still in cache and for the
> news:pSepd.2111$cE3.1783@.clgrps12...
> I
> I
> next
> It's
>
|||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:
> 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...
>
|||Thank you guys for all that information
Alex
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41A77B97.DA602FD7@.toomuchspamalready.nl...[vbcol=seagreen]
> JXStern wrote:
It's
> 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

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 "recompi
le". 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 sti
ll 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...[v
bcol=seagreen]
> 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 ne
xt
> 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
>[/vbcol]|||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...
I[vbcol=seagreen]
I[vbcol=seagreen]
next[vbcol=seagreen]
It's[vbcol=seagreen]
>|||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 i
n
> message news:%23Wuoqeu0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> "recompile". If you drop and
> you get a compilation. Same
> still in cache and for the
> news:pSepd.2111$cE3.1783@.clgrps12...
> I
> I
> next
> It's
>|||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:
> 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...
>|||Thank you guys for all that information
Alex
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41A77B97.DA602FD7@.toomuchspamalready.nl...
> JXStern wrote:
It's[vbcol=seagreen]
> 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