Compiling and query plans question

Question

Compiling and query plans question

Jeff W

SSCertifiable

Points: 5207
More actions
November 17, 2004 at 8:21 am

#87056

Okay Einsteins, I have a question regarding query plans and compiling stored procedures. Is there a difference between the two?
When a stored procedure is compiled is the query plan created? Or is the query plan something completely different?

Thanks my fellow database freinds!
Jeff

"Keep Your Stick On the Ice" ..Red Green

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

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

Adam Wilbur SSCertifiable Points: 7001 More actions · Answer 1

The query execution plan is different than the compilation of a stored procedure. The execution of statements in SQL Server consists of two phases: compilation and execution

The query plan is the path of execution that a particular query or stored procedure takes.

When a stored procedure is compiled, it validates the syntax and object names in the Transact-SQL code.

A.J.
DBA with an attitude

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

When a procedure is run it will generate a query plan based upon the current stats ( etc ) for the underlying tables and so on. This plan is stored in cache for re-use ( hopefully ) - I guess this is what we mean by compiling in as much as the optimiser doesn't have to re-evaluate the query so execution should be quicker.

So I guess you could say the query ( or execution&nbsp plan ( that is placed in the procedure cache ) is the result of compiling the procedure.

In reality it's not always quite so simple, a number of reasons may force your procedure to recompile ( figure out a new plan ), sometimes more than once or your procedure cache may evict the plan to clear space.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

...you asked for it, Jeff

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/sqlquerproc.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_queryrecompilation.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_4azp.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp

http://support.microsoft.com/default.aspx?scid=kb;EN-US;836136

And, of course, you can read about it in "Inside SQL Server 2000"

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]