What exactly is a query plan (or a SQL plan for that matter)

I have been using databases (moderately) for a while--just your basic stuff,
but I have been reading a lot of messages that reference query plan's and
SQL generates a plan...  What exactly does this mean?   Is there any good
reference material that covers what exactly this is?

I think I understand a little bit about it, in that the plan is just a way
of executing the instructions in the best way it can, but I am curious to
know some of the logic involved in determining what is best?

Thanks,

"Tim Mavers" <webview@hotmail.com> writes:

> I have been using databases (moderately) for a while--just your basic stuf
f,
> but I have been reading a lot of messages that reference query plan's and
> SQL generates a plan...  What exactly does this mean?   Is there any good
> reference material that covers what exactly this is?
>
> I think I understand a little bit about it, in that the plan is just a way
> of executing the instructions in the best way it can, but I am curious to
> know some of the logic involved in determining what is best?

A plan is exactly that, a plan. The plan is akin to you being told
where in a book your answer is.

If you are told it is on page 23, then you would probably "plan" the
binary searching algorithm. (ie, flip a little bit, am I greater than
23 or less than 23, flip the opposite direction).  But, if the book
were really large, you might immediately "plan" to start near the
beginning of the book, while also "planning" to implementing the
binary search...

But, if you were told that your answer is across the book, and
involves the word, "database", you'd probably "plan" to look through
the book's index for all occurences of the word "database".  (And, as
far as names go, the name "index" is very aptly named)

As you would plan to find something, the optimiser is doing some
analogous type of decision process.  In the end, it chooses amongst
many plans trying to choose the best.

The "plan" is really very aptly named.  Its the optimiser's chosen
"plan" at solving the task at hand.

--
Galen Boyer

The plan includes information for SQL Server's execution engine on *how* the
 query is top be
executed. The SQL language is a descriptive language in the sense that you s
ay *what* to be
performed, now *how* to perform (of execute) the query. So, the optimizer lo
oks at each DML
statement (SELECT, INSERT, UPDATE, DELETE) and determine which index (if any
) to use, how to perform
joins (nested loop, hash or merge joins), how to do grouping, UNION etc.

I'd say that any SQL Server book at moderate or advanced level should includ
e some stuff on this.
I'd probably look in "Inside SQL Server" first.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/


"Tim Mavers" <webview@hotmail.com> wrote in message news:VO-dnXqkG_gn6mvcRVn-qA@giganews.co
m...
>I have been using databases (moderately) for a while--just your basic stuff
, but I have been
>reading a lot of messages that reference query plan's and SQL generates a p
lan...  What exactly
>does this mean?   Is there any good reference material that covers what exa
ctly this is?
>
> I think I understand a little bit about it, in that the plan is just a way
 of executing the
> instructions in the best way it can, but I am curious to know some of the 
logic involved in
> determining what is best?
>
> Thanks,
>
>

Hello Tim,

You may want to read this whitepaper which talks about how 2005 and 2000
us the plan cache and what causes a new plan to be created.

Summary: This paper explains how batches are cached and reused in SQL Server
2005, and suggests best practices on maximizing reuse of cached plans. It
also explains scenarios in which batches are recompiled, and gives best prac
tices
for reducing or eliminating unnecessary recompilations.

http://www.microsoft.com/technet/pr...005/recomp.mspx

--
Aaron Weiker
http://aaronweiker.com/

> I have been using databases (moderately) for a while--just your basic
> stuff, but I have been reading a lot of messages that reference query
> plan's and SQL generates a plan...  What exactly does this mean?   Is
> there any good reference material that covers what exactly this is?
>
> I think I understand a little bit about it, in that the plan is just a
> way of executing the instructions in the best way it can, but I am
> curious to know some of the logic involved in determining what is
> best?
>
> Thanks,
>

I read a little more in "Inside SQL Server" about how the query optimizer
breaks down the query into smaller queries.  Does it stat to cache these
queries?  This sounds similar to the way a CPU caches instructions.  In that
it is at a much lower level, more 'requests' will benefit from the cache.

I guess I am just trying to understand (at a lower level) when a query is
cached and if stored procs tend to benefit more caching than dynamic SQL.
To me, it sounds like if you are simply passing the same query via pass-thru
SQL (i.e. you can pass "select * from mytable" with EXEC or put it in a
stored proc, the performance difference is minor).  Is this correct?

Tim

"Aaron Weiker" <aaron@sqlprogrammer.org> wrote in message
news:128929632422523976830603@news.microsoft.com...
> Hello Tim,
>
> You may want to read this whitepaper which talks about how 2005 and 2000
> us the plan cache and what causes a new plan to be created.
>
> Summary: This paper explains how batches are cached and reused in SQL
> Server 2005, and suggests best practices on maximizing reuse of cached
> plans. It also explains scenarios in which batches are recompiled, and
> gives best practices for reducing or eliminating unnecessary
> recompilations.
> http://www.microsoft.com/technet/pr...005/recomp.mspx
>
> --
> Aaron Weiker
> http://aaronweiker.com/
> 
>
>

Hello Tim,
The performance benefit comes from the same procedure getting ran multiple
times. If it had to recompile the plan each time the query was run and you
ran this query several tims a second you would be losing significant CPU
speed. Now if you only run this query a couple times an hour you are not
going to benefit as much from having the plan cached. This doesn't mean that
you shouldn't use stored procedures if your not executing the script very
regularly as there are other advantages to stored procedures besides the
plan cache.

Hope this helps.

--
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> I read a little more in "Inside SQL Server" about how the query
> optimizer breaks down the query into smaller queries.  Does it stat to
> cache these queries?  This sounds similar to the way a CPU caches
> instructions.  In that it is at a much lower level, more 'requests'
> will benefit from the cache.
>
> I guess I am just trying to understand (at a lower level) when a query
> is cached and if stored procs tend to benefit more caching than
> dynamic SQL. To me, it sounds like if you are simply passing the same
> query via pass-thru SQL (i.e. you can pass "select * from mytable"
> with EXEC or put it in a stored proc, the performance difference is
> minor).  Is this correct?
>
> Tim
>
> "Aaron Weiker" <aaron@sqlprogrammer.org> wrote in message
> news:128929632422523976830603@news.microsoft.com...
>