Friday, March 23, 2012

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
>

No comments:

Post a Comment