optimizer & load

So, we know that the optimizer may choose certain join types under different
memory loads or pressures, but will the optimizer vary the degree of
paralellism based on the number of already active threads or the system cpu
load?
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----Not at run time... At most the optimizer will output 2 plans, one serial and
one parallell... If for any reason there are not enough resources at run
time to do the parallel plan, the serial plan is run... SQL does not
currently back off of the number of processors, etc...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> So, we know that the optimizer may choose certain join types under
different
> memory loads or pressures, but will the optimizer vary the degree of
> paralellism based on the number of already active threads or the system
cpu
> load?
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
>|||No, is based only upon cost of the query, not current activity not current
load.
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> So, we know that the optimizer may choose certain join types under
different
> memory loads or pressures, but will the optimizer vary the degree of
> paralellism based on the number of already active threads or the system
cpu
> load?
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
>|||> No, is based only upon cost of the query, not current activity not
current
> load.
From the SQL Server 2000 Books Online:
<Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
SQL Server monitors CPU usage and adjusts the degree of parallelism at
the query startup time. Lower degrees of parallelism are chosen if CPU
usage is high.
</Excerpt>
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> No, is based only upon cost of the query, not current activity not
current
> load.
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > So, we know that the optimizer may choose certain join types under
> different
> > memory loads or pressures, but will the optimizer vary the degree of
> > paralellism based on the number of already active threads or the
system
> cpu
> > load?
> >
> > --
> > Kevin Connell, MCDBA
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> >
> >
>|||That's what I was looking for. Maybe I'll send a request for a "knob" on
this one to sqlwish :)
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:eLpn4XvhDHA.944@.TK2MSFTNGP11.phx.gbl...
> > No, is based only upon cost of the query, not current activity not
> current
> > load.
> From the SQL Server 2000 Books Online:
> <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> SQL Server monitors CPU usage and adjusts the degree of parallelism at
> the query startup time. Lower degrees of parallelism are chosen if CPU
> usage is high.
> </Excerpt>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > No, is based only upon cost of the query, not current activity not
> current
> > load.
> >
> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > So, we know that the optimizer may choose certain join types under
> > different
> > > memory loads or pressures, but will the optimizer vary the degree of
> > > paralellism based on the number of already active threads or the
> system
> > cpu
> > > load?
> > >
> > > --
> > > Kevin Connell, MCDBA
> > > ----
> > > The views expressed here are my own
> > > and not of my employer.
> > > ----
> > >
> > >
> >
> >
>|||Dan,
That is interesting. Do you know if any number of CPU's is being
considered? Or will SQL-Server scale back to a fixed number of CPU's
(for example 2, 4, 8) or will it simply scale back to 1 (and use the
serial query plan)?
I assume the degree of parallism is determined before the query plan for
this number of CPU's is compiled (or fetched from cache).
Gert-Jan
Dan Guzman wrote:
> > No, is based only upon cost of the query, not current activity not
> current
> > load.
> From the SQL Server 2000 Books Online:
> <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> SQL Server monitors CPU usage and adjusts the degree of parallelism at
> the query startup time. Lower degrees of parallelism are chosen if CPU
> usage is high.
> </Excerpt>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > No, is based only upon cost of the query, not current activity not
> current
> > load.
> >
> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > So, we know that the optimizer may choose certain join types under
> > different
> > > memory loads or pressures, but will the optimizer vary the degree of
> > > paralellism based on the number of already active threads or the
> system
> > cpu
> > > load?
> > >
> > > --
> > > Kevin Connell, MCDBA
> > > ----
> > > The views expressed here are my own
> > > and not of my employer.
> > > ----
> > >
> > >
> >
> >|||I'm planning on doing some testing. Should be pretty straightforward to
figure out.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F79D788.139C5712@.toomuchspamalready.nl...
> Dan,
> That is interesting. Do you know if any number of CPU's is being
> considered? Or will SQL-Server scale back to a fixed number of CPU's
> (for example 2, 4, 8) or will it simply scale back to 1 (and use the
> serial query plan)?
> I assume the degree of parallism is determined before the query plan for
> this number of CPU's is compiled (or fetched from cache).
> Gert-Jan
>
> Dan Guzman wrote:
> >
> > > No, is based only upon cost of the query, not current activity not
> > current
> > > load.
> >
> > From the SQL Server 2000 Books Online:
> >
> > <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> >
> > SQL Server monitors CPU usage and adjusts the degree of parallelism at
> > the query startup time. Lower degrees of parallelism are chosen if CPU
> > usage is high.
> >
> > </Excerpt>
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> > news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > > No, is based only upon cost of the query, not current activity not
> > current
> > > load.
> > >
> > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > > So, we know that the optimizer may choose certain join types under
> > > different
> > > > memory loads or pressures, but will the optimizer vary the degree of
> > > > paralellism based on the number of already active threads or the
> > system
> > > cpu
> > > > load?
> > > >
> > > > --
> > > > Kevin Connell, MCDBA
> > > > ----
> > > > The views expressed here are my own
> > > > and not of my employer.
> > > > ----
> > > >
> > > >
> > >
> > >|||> That is interesting. Do you know if any number of CPU's is being
> considered? Or will SQL-Server scale back to a fixed number of CPU's
> (for example 2, 4, 8) or will it simply scale back to 1 (and use the
> serial query plan)?
> I assume the degree of parallism is determined before the query plan
for
> this number of CPU's is compiled (or fetched from cache).
My interpretation of the following (from the same topic) is that the
number of CPUs is variable and the optimal number of threads is
reevaluated for each execution:
<Excerpt>
SQL Server reexamines the optimal number of thread decisions each time a
query execution plan is retrieved from the procedure cache. For example,
one execution of a query can result in use of a serial plan, a later
execution of the same query can result in a parallel plan using three
threads, and a third execution can result in a parallel plan using four
threads.
</Excerpt>
I haven't played around with this, though. Hopefully, Kevin will post
the results of his tests.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F79D788.139C5712@.toomuchspamalready.nl...
> Dan,
> That is interesting. Do you know if any number of CPU's is being
> considered? Or will SQL-Server scale back to a fixed number of CPU's
> (for example 2, 4, 8) or will it simply scale back to 1 (and use the
> serial query plan)?
> I assume the degree of parallism is determined before the query plan
for
> this number of CPU's is compiled (or fetched from cache).
> Gert-Jan
>
> Dan Guzman wrote:
> >
> > > No, is based only upon cost of the query, not current activity not
> > current
> > > load.
> >
> > From the SQL Server 2000 Books Online:
> >
> > <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> >
> > SQL Server monitors CPU usage and adjusts the degree of parallelism
at
> > the query startup time. Lower degrees of parallelism are chosen if
CPU
> > usage is high.
> >
> > </Excerpt>
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> > news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > > No, is based only upon cost of the query, not current activity not
> > current
> > > load.
> > >
> > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > > So, we know that the optimizer may choose certain join types
under
> > > different
> > > > memory loads or pressures, but will the optimizer vary the
degree of
> > > > paralellism based on the number of already active threads or the
> > system
> > > cpu
> > > > load?
> > > >
> > > > --
> > > > Kevin Connell, MCDBA
> > > > ----
> > > > The views expressed here are my own
> > > > and not of my employer.
> > > > ----
> > > >
> > > >
> > >
> > >|||> I'm planning on doing some testing. Should be pretty straightforward
to
> figure out.
Please share your results with the group.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:u7Ux3%238hDHA.944@.TK2MSFTNGP11.phx.gbl...
> I'm planning on doing some testing. Should be pretty straightforward
to
> figure out.
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:3F79D788.139C5712@.toomuchspamalready.nl...
> > Dan,
> >
> > That is interesting. Do you know if any number of CPU's is being
> > considered? Or will SQL-Server scale back to a fixed number of CPU's
> > (for example 2, 4, 8) or will it simply scale back to 1 (and use the
> > serial query plan)?
> >
> > I assume the degree of parallism is determined before the query plan
for
> > this number of CPU's is compiled (or fetched from cache).
> >
> > Gert-Jan
> >
> >
> > Dan Guzman wrote:
> > >
> > > > No, is based only upon cost of the query, not current activity
not
> > > current
> > > > load.
> > >
> > > From the SQL Server 2000 Books Online:
> > >
> > > <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> > >
> > > SQL Server monitors CPU usage and adjusts the degree of
parallelism at
> > > the query startup time. Lower degrees of parallelism are chosen if
CPU
> > > usage is high.
> > >
> > > </Excerpt>
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > --
> > > SQL FAQ links (courtesy Neil Pike):
> > >
> > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > http://www.sqlserverfaq.com
> > > http://www.mssqlserver.com/faq
> > > --
> > >
> > > "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> > > news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > > > No, is based only upon cost of the query, not current activity
not
> > > current
> > > > load.
> > > >
> > > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > > > So, we know that the optimizer may choose certain join types
under
> > > > different
> > > > > memory loads or pressures, but will the optimizer vary the
degree of
> > > > > paralellism based on the number of already active threads or
the
> > > system
> > > > cpu
> > > > > load?
> > > > >
> > > > > --
> > > > > Kevin Connell, MCDBA
> > > > > ----
> > > > > The views expressed here are my own
> > > > > and not of my employer.
> > > > > ----
> > > > >
> > > > >
> > > >
> > > >
>|||Either my test is no good or the documentation is incorrect. DOP is not
affected by system CPU load.
I've been running a machine at 100% CPU load for hours now and large
paralell queries continue to come in at DOP=4
Environment:
4 CPU ML-570G2 (4x2.0Ghz XEON P4)
Win2k Std Sp3
SQL 2K Enterprise Sp3+ms03-031
4GB RAM
sp_config:maxdop = 0
Measuring DOP with profiler event class "Degree of Paralellism" and
verifying execution plan is paralell via QA.
Would love to know if someone else can verify these findings.
Kevin Connell, MCDBA
----
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23zdG5CIiDHA.2192@.TK2MSFTNGP09.phx.gbl...
> > I'm planning on doing some testing. Should be pretty straightforward
> to
> > figure out.
> Please share your results with the group.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:u7Ux3%238hDHA.944@.TK2MSFTNGP11.phx.gbl...
> > I'm planning on doing some testing. Should be pretty straightforward
> to
> > figure out.
> >
> >
> > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > news:3F79D788.139C5712@.toomuchspamalready.nl...
> > > Dan,
> > >
> > > That is interesting. Do you know if any number of CPU's is being
> > > considered? Or will SQL-Server scale back to a fixed number of CPU's
> > > (for example 2, 4, 8) or will it simply scale back to 1 (and use the
> > > serial query plan)?
> > >
> > > I assume the degree of parallism is determined before the query plan
> for
> > > this number of CPU's is compiled (or fetched from cache).
> > >
> > > Gert-Jan
> > >
> > >
> > > Dan Guzman wrote:
> > > >
> > > > > No, is based only upon cost of the query, not current activity
> not
> > > > current
> > > > > load.
> > > >
> > > > From the SQL Server 2000 Books Online:
> > > >
> > > > <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> > > >
> > > > SQL Server monitors CPU usage and adjusts the degree of
> parallelism at
> > > > the query startup time. Lower degrees of parallelism are chosen if
> CPU
> > > > usage is high.
> > > >
> > > > </Excerpt>
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > Dan Guzman
> > > > SQL Server MVP
> > > >
> > > > --
> > > > SQL FAQ links (courtesy Neil Pike):
> > > >
> > > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > > http://www.sqlserverfaq.com
> > > > http://www.mssqlserver.com/faq
> > > > --
> > > >
> > > > "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> > > > news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > > > > No, is based only upon cost of the query, not current activity
> not
> > > > current
> > > > > load.
> > > > >
> > > > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > > > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > > > > So, we know that the optimizer may choose certain join types
> under
> > > > > different
> > > > > > memory loads or pressures, but will the optimizer vary the
> degree of
> > > > > > paralellism based on the number of already active threads or
> the
> > > > system
> > > > > cpu
> > > > > > load?
> > > > > >
> > > > > > --
> > > > > > Kevin Connell, MCDBA
> > > > > > ----
> > > > > > The views expressed here are my own
> > > > > > and not of my employer.
> > > > > > ----
> > > > > >
> > > > > >
> > > > >
> > > > >
> >
> >
>|||I'll see what I can find out.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:O7Z4WPViDHA.2592@.tk2msftngp13.phx.gbl...
> Either my test is no good or the documentation is incorrect. DOP is
not
> affected by system CPU load.
> I've been running a machine at 100% CPU load for hours now and large
> paralell queries continue to come in at DOP=4
> Environment:
> 4 CPU ML-570G2 (4x2.0Ghz XEON P4)
> Win2k Std Sp3
> SQL 2K Enterprise Sp3+ms03-031
> 4GB RAM
> sp_config:maxdop = 0
> Measuring DOP with profiler event class "Degree of Paralellism" and
> verifying execution plan is paralell via QA.
> Would love to know if someone else can verify these findings.
> Kevin Connell, MCDBA
> ----
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:%23zdG5CIiDHA.2192@.TK2MSFTNGP09.phx.gbl...
> > > I'm planning on doing some testing. Should be pretty
straightforward
> > to
> > > figure out.
> >
> > Please share your results with the group.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> >
> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > news:u7Ux3%238hDHA.944@.TK2MSFTNGP11.phx.gbl...
> > > I'm planning on doing some testing. Should be pretty
straightforward
> > to
> > > figure out.
> > >
> > >
> > > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > > news:3F79D788.139C5712@.toomuchspamalready.nl...
> > > > Dan,
> > > >
> > > > That is interesting. Do you know if any number of CPU's is being
> > > > considered? Or will SQL-Server scale back to a fixed number of
CPU's
> > > > (for example 2, 4, 8) or will it simply scale back to 1 (and use
the
> > > > serial query plan)?
> > > >
> > > > I assume the degree of parallism is determined before the query
plan
> > for
> > > > this number of CPU's is compiled (or fetched from cache).
> > > >
> > > > Gert-Jan
> > > >
> > > >
> > > > Dan Guzman wrote:
> > > > >
> > > > > > No, is based only upon cost of the query, not current
activity
> > not
> > > > > current
> > > > > > load.
> > > > >
> > > > > From the SQL Server 2000 Books Online:
> > > > >
> > > > > <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> > > > >
> > > > > SQL Server monitors CPU usage and adjusts the degree of
> > parallelism at
> > > > > the query startup time. Lower degrees of parallelism are
chosen if
> > CPU
> > > > > usage is high.
> > > > >
> > > > > </Excerpt>
> > > > >
> > > > > --
> > > > > Hope this helps.
> > > > >
> > > > > Dan Guzman
> > > > > SQL Server MVP
> > > > >
> > > > > --
> > > > > SQL FAQ links (courtesy Neil Pike):
> > > > >
> > > > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > > > http://www.sqlserverfaq.com
> > > > > http://www.mssqlserver.com/faq
> > > > > --
> > > > >
> > > > > "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> > > > > news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > > > > > No, is based only upon cost of the query, not current
activity
> > not
> > > > > current
> > > > > > load.
> > > > > >
> > > > > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > > > > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > > > > > So, we know that the optimizer may choose certain join
types
> > under
> > > > > > different
> > > > > > > memory loads or pressures, but will the optimizer vary the
> > degree of
> > > > > > > paralellism based on the number of already active threads
or
> > the
> > > > > system
> > > > > > cpu
> > > > > > > load?
> > > > > > >
> > > > > > > --
> > > > > > > Kevin Connell, MCDBA
> > > > > > > ----
> > > > > > > The views expressed here are my own
> > > > > > > and not of my employer.
> > > > > > > ----
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > >
> > >
> >
> >
>