Execution plan of Stored Procedures

Question

Post reply

Execution plan of Stored Procedures

Kaushik Majumder

Ten Centuries

Points: 1009
More actions
September 11, 2007 at 5:28 am

#118604

Hi,
Is there any wat to ensure that an SP's execution plan is actually stored in buffer ? Is there any way to view the same?
The SP is not defined with 'Recompile' option.

Regards,
Kaushik

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SimonLiew SSCrazy Eights Points: 8305 More actions · Answer 1

Kaushik,

Are you trying to look at what stored procs are available in the cache buffers?

Not sure if you're trying to look for this

select * from master..syscacheobjects

Simon

Simon Liew
Microsoft Certified Master: SQL Server 2008

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 2

On a busy system, if it's a batch proc, wait 5 minutes after it's done, and it won't be cached any more.

On a busy system, if it's a GUI proc, wait until about 2-5 new rows are added to the table and it will have to recompile.

See Books Online for additional details...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Note it may be there but may still have to recompile. To see if it is using a cached version you really have to use Profiler and look for Cache Misses to see if it has to recompile itself.