Multiprocessors

What are my options for the number of multiprocessors that SQL Server 2000
will run on?
1 - 2 - 4 - 8 - 16?
Is there at some point where throwing more processors at SQL Server won't
make a difference?
Hi,
That depends on the OS version and SQl server version you are using.
SQL server 2000 enterprise edition on a Windows 2000 data center edition
will support 32 Processors (CPU).
See the details in topic "maximum capacity specifications" in books online.
Thanks
Hari
MCDBA
"William Gower" <w_gower@.hotmail.com> wrote in message
news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> What are my options for the number of multiprocessors that SQL Server 2000
> will run on?
> 1 - 2 - 4 - 8 - 16?
> Is there at some point where throwing more processors at SQL Server won't
> make a difference?
>
|||Hi William
Re:
>Is there at some point where throwing more processors at SQL Server won't
make a difference?<
Conceptually not - connections are affinitised (though not hard-wired)
against CPUs by SQL Server's internal scheduling mechanisms, so assuming you
have many more user connections than CPUs, there is generally a benefit to
be gained from the extra CPUs.
In practical terms however, there is a law of diminishing returns in
continually adding CPUs. Where apex of that curve lies depends on many
scenario specifics.
HTH
Regards,
Greg Linwood
SQL Server MVP
"William Gower" <w_gower@.hotmail.com> wrote in message
news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> What are my options for the number of multiprocessors that SQL Server 2000
> will run on?
> 1 - 2 - 4 - 8 - 16?
> Is there at some point where throwing more processors at SQL Server won't
> make a difference?
>
|||They're only affinitized for single threaded queries.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uqV3$0VeEHA.3632@.TK2MSFTNGP09.phx.gbl...
> Hi William
> Re:
> make a difference?<
> Conceptually not - connections are affinitised (though not hard-wired)
> against CPUs by SQL Server's internal scheduling mechanisms, so assuming
you[vbcol=seagreen]
> have many more user connections than CPUs, there is generally a benefit to
> be gained from the extra CPUs.
> In practical terms however, there is a law of diminishing returns in
> continually adding CPUs. Where apex of that curve lies depends on many
> scenario specifics.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "William Gower" <w_gower@.hotmail.com> wrote in message
> news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
2000[vbcol=seagreen]
won't
>
|||Connection objects (spids) are indeed affinitised to cpus by virtue of the
fact that they're owned by ums schedulers which are themselves affinitised.
Worker threads which satisfy parallelism (multi-threaded queries) or other
multi-threaded work are another thing all together - they certainly are not
affinitised.
Regards,
Greg Linwood
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:ePxBvbdeEHA.556@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> They're only affinitized for single threaded queries.
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uqV3$0VeEHA.3632@.TK2MSFTNGP09.phx.gbl...
won't[vbcol=seagreen]
> you
to
> 2000
> won't
>

Multiprocessors

What are my options for the number of multiprocessors that SQL Server 2000
will run on?
1 - 2 - 4 - 8 - 16?
Is there at some point where throwing more processors at SQL Server won't
make a difference?Hi,
That depends on the OS version and SQl server version you are using.
SQL server 2000 enterprise edition on a Windows 2000 data center edition
will support 32 Processors (CPU).
See the details in topic "maximum capacity specifications" in books online.
Thanks
Hari
MCDBA
"William Gower" <w_gower@.hotmail.com> wrote in message
news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> What are my options for the number of multiprocessors that SQL Server 2000
> will run on?
> 1 - 2 - 4 - 8 - 16?
> Is there at some point where throwing more processors at SQL Server won't
> make a difference?
>|||Hi William
Re:
>Is there at some point where throwing more processors at SQL Server won't
make a difference?<
Conceptually not - connections are affinitised (though not hard-wired)
against CPUs by SQL Server's internal scheduling mechanisms, so assuming you
have many more user connections than CPUs, there is generally a benefit to
be gained from the extra CPUs.
In practical terms however, there is a law of diminishing returns in
continually adding CPUs. Where apex of that curve lies depends on many
scenario specifics.
HTH
Regards,
Greg Linwood
SQL Server MVP
"William Gower" <w_gower@.hotmail.com> wrote in message
news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> What are my options for the number of multiprocessors that SQL Server 2000
> will run on?
> 1 - 2 - 4 - 8 - 16?
> Is there at some point where throwing more processors at SQL Server won't
> make a difference?
>|||They're only affinitized for single threaded queries.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uqV3$0VeEHA.3632@.TK2MSFTNGP09.phx.gbl...
> Hi William
> Re:
> make a difference?<
> Conceptually not - connections are affinitised (though not hard-wired)
> against CPUs by SQL Server's internal scheduling mechanisms, so assuming
you
> have many more user connections than CPUs, there is generally a benefit to
> be gained from the extra CPUs.
> In practical terms however, there is a law of diminishing returns in
> continually adding CPUs. Where apex of that curve lies depends on many
> scenario specifics.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "William Gower" <w_gower@.hotmail.com> wrote in message
> news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
2000[vbcol=seagreen]
won't[vbcol=seagreen]
>|||Connection objects (spids) are indeed affinitised to cpus by virtue of the
fact that they're owned by ums schedulers which are themselves affinitised.
Worker threads which satisfy parallelism (multi-threaded queries) or other
multi-threaded work are another thing all together - they certainly are not
affinitised.
Regards,
Greg Linwood
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:ePxBvbdeEHA.556@.tk2msftngp13.phx.gbl...
> They're only affinitized for single threaded queries.
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uqV3$0VeEHA.3632@.TK2MSFTNGP09.phx.gbl...
won't[vbcol=seagreen]
> you
to[vbcol=seagreen]
> 2000
> won't
>

multiprocessors

HI

If the machine running SQL Express has multi-processors (2) - does it have an adverse affect on it's peformance, heavens forbid - do we need to make any settings changes via the Enterprise Manager tool ?

Thanks
bruce

It should not have an adverse affect on perf, actually it should improve it as OS talks will hopefully get scheduled on the processor that SQL is not using.|||

Thanks

Performance seems intermittent for some reason.

One thing I've noticed is how little memory it's using - only about 60 meg for a database of 300 + meg and a few users who are pretty active. The machine is a pretty fast beast with 1 gig ram.

Bruce.

|||

Hmm that might be the OS not being as smart as we would like, you could try setting the processor affinity (if its supported in Express which I can't remember right now).

60 MB does seem a little low, I'm testing a new sample against adventureworks right now and its using 1.2GB on my desktop with a single user... The memory usage is very workload dependent. If perf does not become more predictable try posting back.

Multiprocessors

What are my options for the number of multiprocessors that SQL Server 2000
will run on?
1 - 2 - 4 - 8 - 16?
Is there at some point where throwing more processors at SQL Server won't
make a difference?Hi,
That depends on the OS version and SQl server version you are using.
SQL server 2000 enterprise edition on a Windows 2000 data center edition
will support 32 Processors (CPU).
See the details in topic "maximum capacity specifications" in books online.
Thanks
Hari
MCDBA
"William Gower" <w_gower@.hotmail.com> wrote in message
news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> What are my options for the number of multiprocessors that SQL Server 2000
> will run on?
> 1 - 2 - 4 - 8 - 16?
> Is there at some point where throwing more processors at SQL Server won't
> make a difference?
>|||Hi William
Re:
>Is there at some point where throwing more processors at SQL Server won't
make a difference?<
Conceptually not - connections are affinitised (though not hard-wired)
against CPUs by SQL Server's internal scheduling mechanisms, so assuming you
have many more user connections than CPUs, there is generally a benefit to
be gained from the extra CPUs.
In practical terms however, there is a law of diminishing returns in
continually adding CPUs. Where apex of that curve lies depends on many
scenario specifics.
HTH
Regards,
Greg Linwood
SQL Server MVP
"William Gower" <w_gower@.hotmail.com> wrote in message
news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> What are my options for the number of multiprocessors that SQL Server 2000
> will run on?
> 1 - 2 - 4 - 8 - 16?
> Is there at some point where throwing more processors at SQL Server won't
> make a difference?
>|||They're only affinitized for single threaded queries.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uqV3$0VeEHA.3632@.TK2MSFTNGP09.phx.gbl...
> Hi William
> Re:
> >Is there at some point where throwing more processors at SQL Server won't
> make a difference?<
> Conceptually not - connections are affinitised (though not hard-wired)
> against CPUs by SQL Server's internal scheduling mechanisms, so assuming
you
> have many more user connections than CPUs, there is generally a benefit to
> be gained from the extra CPUs.
> In practical terms however, there is a law of diminishing returns in
> continually adding CPUs. Where apex of that curve lies depends on many
> scenario specifics.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "William Gower" <w_gower@.hotmail.com> wrote in message
> news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> > What are my options for the number of multiprocessors that SQL Server
2000
> > will run on?
> >
> > 1 - 2 - 4 - 8 - 16?
> >
> > Is there at some point where throwing more processors at SQL Server
won't
> > make a difference?
> >
> >
>|||Connection objects (spids) are indeed affinitised to cpus by virtue of the
fact that they're owned by ums schedulers which are themselves affinitised.
Worker threads which satisfy parallelism (multi-threaded queries) or other
multi-threaded work are another thing all together - they certainly are not
affinitised.
Regards,
Greg Linwood
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:ePxBvbdeEHA.556@.tk2msftngp13.phx.gbl...
> They're only affinitized for single threaded queries.
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uqV3$0VeEHA.3632@.TK2MSFTNGP09.phx.gbl...
> > Hi William
> >
> > Re:
> > >Is there at some point where throwing more processors at SQL Server
won't
> > make a difference?<
> >
> > Conceptually not - connections are affinitised (though not hard-wired)
> > against CPUs by SQL Server's internal scheduling mechanisms, so assuming
> you
> > have many more user connections than CPUs, there is generally a benefit
to
> > be gained from the extra CPUs.
> >
> > In practical terms however, there is a law of diminishing returns in
> > continually adding CPUs. Where apex of that curve lies depends on many
> > scenario specifics.
> >
> > HTH
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "William Gower" <w_gower@.hotmail.com> wrote in message
> > news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> > > What are my options for the number of multiprocessors that SQL Server
> 2000
> > > will run on?
> > >
> > > 1 - 2 - 4 - 8 - 16?
> > >
> > > Is there at some point where throwing more processors at SQL Server
> won't
> > > make a difference?
> > >
> > >
> >
> >
>