Prev: install sp1 on sql 2008 r2
Next: SQL Standards
From: Simon on 5 May 2010 08:46
Hi guys,

Can anyone tell me if the query optimiser takes available system memory
into account when deciding how to perform a given query.

To take an extreme example, could it potentially come up with a
different plan for a server with 256G or RAM versus a server with 2GB of
RAM - simply as a result of the amount of extra RAM available?

Similarly, is the relative load on the server at any given moment ever
used to modify how the server will carry out a query at run time?

Many thanks

Simon
From: Andrew J. Kelly on 5 May 2010 09:03
AFAIK memory is not a factor in deciding the initial plan. But things like
available memory, number of procs, how busy the system is etc. are evaluated
during runtime and may influence certain aspects. For instance if the
initial plan was determined to be costly enough to warrant parallelism it
will get a single threaded and parallel plan generated. But at runtime the
current conditions will dictate if a single thread or multiple threads and
how many are used. Available Memory will affect things like if the query has
enough memory to even run vs. waiting until memory is available. But the
plan is the same. It may also spill to tempdb if short on memory as well.
You may want to have a look at these:

http://msdn.microsoft.com/en-us/library/ee343986.aspx
http://msdn.microsoft.com/en-us/library/dd535534.aspx
http://blogs.msdn.com/queryoptteam/


--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Simon" <nothanks(a)hotmail.com> wrote in message
news:#w3GLEF7KHA.5644(a)TK2MSFTNGP04.phx.gbl...
> Hi guys,
>
> Can anyone tell me if the query optimiser takes available system memory
> into account when deciding how to perform a given query.
>
> To take an extreme example, could it potentially come up with a different
> plan for a server with 256G or RAM versus a server with 2GB of RAM -
> simply as a result of the amount of extra RAM available?
>
> Similarly, is the relative load on the server at any given moment ever
> used to modify how the server will carry out a query at run time?
>
> Many thanks
>
> Simon

From: Simon H on 5 May 2010 15:08
Hi Andrew,

Thanks for the reply - that was my understanding as well.

For some reason I had the feeling that most physical aspects of the
server wouldn't change the plan that gets generated. The main exception
to this perhaps being on a system with multiple CPU's you might get a
parallel plan.

Good point on the availability of memory delaying the execution of the
query though. I'm trying to get a list of runtime things that can modify
the way a query runs. The delay or spooling to tempdb is a good one to
bear in mind

Many thanks for your help

S



Andrew J. Kelly wrote:
> AFAIK memory is not a factor in deciding the initial plan. But things
> like available memory, number of procs, how busy the system is etc. are
> evaluated during runtime and may influence certain aspects. For instance
> if the initial plan was determined to be costly enough to warrant
> parallelism it will get a single threaded and parallel plan generated.
> But at runtime the current conditions will dictate if a single thread or
> multiple threads and how many are used. Available Memory will affect
> things like if the query has enough memory to even run vs. waiting until
> memory is available. But the plan is the same. It may also spill to
> tempdb if short on memory as well. You may want to have a look at these:
>
> http://msdn.microsoft.com/en-us/library/ee343986.aspx
> http://msdn.microsoft.com/en-us/library/dd535534.aspx
> http://blogs.msdn.com/queryoptteam/
>
>
From: Gert-Jan Strik on 8 May 2010 15:03
In addition to Andrew's response, the server load and available memory
can also affect the locking granularity as well as the thresshold to
escalate from row/page lock to a table lock.

--
Gert-Jan


Simon H wrote:
>
> Hi Andrew,
>
> Thanks for the reply - that was my understanding as well.
>
> For some reason I had the feeling that most physical aspects of the
> server wouldn't change the plan that gets generated. The main exception
> to this perhaps being on a system with multiple CPU's you might get a
> parallel plan.
>
> Good point on the availability of memory delaying the execution of the
> query though. I'm trying to get a list of runtime things that can modify
> the way a query runs. The delay or spooling to tempdb is a good one to
> bear in mind
>
> Many thanks for your help
>
> S
>
> Andrew J. Kelly wrote:
> > AFAIK memory is not a factor in deciding the initial plan. But things
> > like available memory, number of procs, how busy the system is etc. are
> > evaluated during runtime and may influence certain aspects. For instance
> > if the initial plan was determined to be costly enough to warrant
> > parallelism it will get a single threaded and parallel plan generated.
> > But at runtime the current conditions will dictate if a single thread or
> > multiple threads and how many are used. Available Memory will affect
> > things like if the query has enough memory to even run vs. waiting until
> > memory is available. But the plan is the same. It may also spill to
> > tempdb if short on memory as well. You may want to have a look at these:
> >
> > http://msdn.microsoft.com/en-us/library/ee343986.aspx
> > http://msdn.microsoft.com/en-us/library/dd535534.aspx
> > http://blogs.msdn.com/queryoptteam/
> >
> >
 | 
Pages: 1
Prev: install sp1 on sql 2008 r2
Next: SQL Standards