Query plan and Execution plan

What is the difference between query plan and execution plan for stored
procedure?Can you give us some context? Or a link perhaps? This is what I think they mean:
Each cached procedure has a cache entry for the procedure. This is the execution plan for the
procedure.
Each DML statement inside the procedure has a plan, cached. This is the query plan.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:ODjAp9q7HHA.1188@.TK2MSFTNGP04.phx.gbl...
> What is the difference between query plan and execution plan for stored
> procedure?
>|||That's from the book:
MCTS Exam 70-431 Self-Paced Microsoft SQL Server 2005 Implementation and
Maintenance
By Microsoft Press, Solid Quality Learning
ISBN: 978-0-7356-2271-5
This is when talk about the RECOMPILE option when create the Stored
Procedure:
"Stored procedures are compiled into the query cache when executed.
Compilation creates a query plan as well as an execution plan. SQL Server
can reuse the query plan for subsequent executions, which conserves
resources. But the RECOMPILE option forces SQL Server to discard the query
plan each time the procedure is executed and create a new query plan."
> Can you give us some context? Or a link perhaps? This is what I think they
mean:
> Each cached procedure has a cache entry for the procedure. This is the
execution plan for the
> procedure.
> Each DML statement inside the procedure has a plan, cached. This is the
query plan.|||Alan
1) SQL Server may or may not generate for each query its execution plan and
store it in the cache. For stored procedure as usually it generates one
execution plan which could be resused
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:ODjAp9q7HHA.1188@.TK2MSFTNGP04.phx.gbl...
> What is the difference between query plan and execution plan for stored
> procedure?
>|||OK. A stored procedure has both the compiled plan and also when actually used an "execution
context". The later is sometimes referred to as an "execution plan".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:OJq3pwr7HHA.1188@.TK2MSFTNGP04.phx.gbl...
> That's from the book:
> MCTS Exam 70-431 Self-Paced Microsoft SQL Server 2005 Implementation and
> Maintenance
> By Microsoft Press, Solid Quality Learning
> ISBN: 978-0-7356-2271-5
> This is when talk about the RECOMPILE option when create the Stored
> Procedure:
>
> "Stored procedures are compiled into the query cache when executed.
> Compilation creates a query plan as well as an execution plan. SQL Server
> can reuse the query plan for subsequent executions, which conserves
> resources. But the RECOMPILE option forces SQL Server to discard the query
> plan each time the procedure is executed and create a new query plan."
>
>
>> Can you give us some context? Or a link perhaps? This is what I think they
> mean:
>> Each cached procedure has a cache entry for the procedure. This is the
> execution plan for the
>> procedure.
>> Each DML statement inside the procedure has a plan, cached. This is the
> query plan.
>
>|||On Sep 4, 10:35 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> OK. A stored procedure has both the compiled plan and also when actually used an "execution
> context". The later is sometimes referred to as an "execution plan".
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Alan T" <alanNOSPAMpl...@.yahoo.com.au> wrote in message
> news:OJq3pwr7HHA.1188@.TK2MSFTNGP04.phx.gbl...
>
> > That's from the book:
> > MCTS Exam70-431Self-Paced Microsoft SQL Server 2005 Implementation and
> > Maintenance
> > By Microsoft Press, Solid Quality Learning
> > ISBN: 978-0-7356-2271-5
> > This is when talk about the RECOMPILE option when create the Stored
> > Procedure:
> > "Stored procedures are compiled into the query cache when executed.
> > Compilation creates a query plan as well as an execution plan. SQL Server
> > can reuse the query plan for subsequent executions, which conserves
> > resources. But the RECOMPILE option forces SQL Server to discard the query
> > plan each time the procedure is executed and create a new query plan."
> >> Can you give us some context? Or a link perhaps? This is what I think they
> > mean:
> >> Each cached procedure has a cache entry for the procedure. This is the
> > execution plan for the
> >> procedure.
> >> Each DML statement inside the procedure has a plan, cached. This is the
> > query plan.- Hide quoted text -
> - Show quoted text -
I am new with SQL Server and learning on 2005, what would be the
benefit of using the recompile option?|||> I am new with SQL Server and learning on 2005, what would be the
> benefit of using the recompile option?
Above and much more is explained in:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<kjimenez@.gmail.com> wrote in message news:1188933011.438378.81120@.y42g2000hsy.googlegroups.com...
> On Sep 4, 10:35 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> OK. A stored procedure has both the compiled plan and also when actually used an "execution
>> context". The later is sometimes referred to as an "execution plan".
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "Alan T" <alanNOSPAMpl...@.yahoo.com.au> wrote in message
>> news:OJq3pwr7HHA.1188@.TK2MSFTNGP04.phx.gbl...
>>
>> > That's from the book:
>> > MCTS Exam70-431Self-Paced Microsoft SQL Server 2005 Implementation and
>> > Maintenance
>> > By Microsoft Press, Solid Quality Learning
>> > ISBN: 978-0-7356-2271-5
>> > This is when talk about the RECOMPILE option when create the Stored
>> > Procedure:
>> > "Stored procedures are compiled into the query cache when executed.
>> > Compilation creates a query plan as well as an execution plan. SQL Server
>> > can reuse the query plan for subsequent executions, which conserves
>> > resources. But the RECOMPILE option forces SQL Server to discard the query
>> > plan each time the procedure is executed and create a new query plan."
>> >> Can you give us some context? Or a link perhaps? This is what I think they
>> > mean:
>> >> Each cached procedure has a cache entry for the procedure. This is the
>> > execution plan for the
>> >> procedure.
>> >> Each DML statement inside the procedure has a plan, cached. This is the
>> > query plan.- Hide quoted text -
>> - Show quoted text -
>
> I am new with SQL Server and learning on 2005, what would be the
> benefit of using the recompile option?
>