Effect of adding additional CPUs

We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
Enterprise.
What will be the effect on SQL server performance if we add more CPUs ?
Are there any calulations (or numbers available) we can do to determine the
effect of additional CPUs on SQLl server performace ( no of threads, no of
user connections etc)
--
RKIt has a lot to do with what it is you are doing and how you are doing it.
Since that varies so much there is little a std calculation will achieve.
With more CPU's you have the potential to handle more individual
transactions concurrently. You can also potentially do multi-threaded
operations faster such as DBREINDEX and CHECKDB etc. But that does not
guarantee it will. Do you have processor queue issues now?
--
Andrew J. Kelly SQL MVP
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:B616692D-BB49-4038-9441-EE9F0756ED67@.microsoft.com...
> We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
> Enterprise.
> What will be the effect on SQL server performance if we add more CPUs ?
> Are there any calulations (or numbers available) we can do to determine
> the
> effect of additional CPUs on SQLl server performace ( no of threads, no of
> user connections etc)
> --
> RK|||no I do not have preocessor queue issues as of now. Ours is a web application
with lot of selects but few insert/updates (hotel reservations). The reason I
am doing this is for future growth. The product has to be able to support
five times the current load. I am guessing increasing the load by 5 time will
definately cause CPU queue issues. But Increasing the CPUs- is it a good
route to take to handle more load ? Any good reading material on this
subject.
--
RK
"Andrew J. Kelly" wrote:
> It has a lot to do with what it is you are doing and how you are doing it.
> Since that varies so much there is little a std calculation will achieve.
> With more CPU's you have the potential to handle more individual
> transactions concurrently. You can also potentially do multi-threaded
> operations faster such as DBREINDEX and CHECKDB etc. But that does not
> guarantee it will. Do you have processor queue issues now?
> --
> Andrew J. Kelly SQL MVP
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:B616692D-BB49-4038-9441-EE9F0756ED67@.microsoft.com...
> > We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
> > Enterprise.
> >
> > What will be the effect on SQL server performance if we add more CPUs ?
> > Are there any calulations (or numbers available) we can do to determine
> > the
> > effect of additional CPUs on SQLl server performace ( no of threads, no of
> > user connections etc)
> > --
> > RK
>
>|||Increasing the CPU's is always a good way to deal with increased load but
you need to do a few things first.
Make sure all the code and tables are fully optimized. A bad query or lack
of index can drag down a good server fast.
Make sure you have enough ram to keep the relevant data in cache.
Have a properly configured disk subsystem.
Set the MAXDOP at the server level to less than the total number of procs to
allow concurrent queries in peak times.
--
Andrew J. Kelly SQL MVP
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:1B595AAC-B34F-4414-8AF4-C6F664171AF0@.microsoft.com...
> no I do not have preocessor queue issues as of now. Ours is a web
> application
> with lot of selects but few insert/updates (hotel reservations). The
> reason I
> am doing this is for future growth. The product has to be able to support
> five times the current load. I am guessing increasing the load by 5 time
> will
> definately cause CPU queue issues. But Increasing the CPUs- is it a good
> route to take to handle more load ? Any good reading material on this
> subject.
> --
> RK
>
> "Andrew J. Kelly" wrote:
>> It has a lot to do with what it is you are doing and how you are doing
>> it.
>> Since that varies so much there is little a std calculation will achieve.
>> With more CPU's you have the potential to handle more individual
>> transactions concurrently. You can also potentially do multi-threaded
>> operations faster such as DBREINDEX and CHECKDB etc. But that does not
>> guarantee it will. Do you have processor queue issues now?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "RK73" <RK73@.discussions.microsoft.com> wrote in message
>> news:B616692D-BB49-4038-9441-EE9F0756ED67@.microsoft.com...
>> > We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
>> > Enterprise.
>> >
>> > What will be the effect on SQL server performance if we add more CPUs ?
>> > Are there any calulations (or numbers available) we can do to determine
>> > the
>> > effect of additional CPUs on SQLl server performace ( no of threads, no
>> > of
>> > user connections etc)
>> > --
>> > RK
>>|||What server do you have? We were running 500 users on a Dell 8450 with 4
900Mhz cpus.
It failed a month ago and we had to quickly swap in a modern Compaq with 2 x
3.2ghz hyperthreading cpu's.
The dell had 24 disks running raid 10 arrays in two split bus PV200s off two
perc3/dc controllers.
The compaq has only 6 disks but just wipes the floor with the old dell.
While I've had the dell down I tried replacing the Dell Perc3's with the
latest compaq controllers. Dispite the controllers being U320 they have to
run U160 as the 15K rpm Fujitsu drives in the dell are a few years old. The
sustained disk throughput increased by a factor of 2 both for reads and
writes. I was never happy with the perc3's but now the compaq controllers
proved the point. Note also that dell array manager does not set up raid 10
correctly as a stripe of mirrors. It sets it up as a span of mirrors. You
have to set up the raid 10 arrays through the dell controller bios to
achieve true raid 10.
Stats, all on a dell 8450, Windows 2003, Raid 10 on 8 disks over two
channels (dual channel controllers). Write back cache enabled. all stats
unbuffered by windows.
Test tool Sisoft Sandra.
Perc3/DC. Set up by array manager
Sequential Read 33 Mb/sec
Random Read 11 Mb/sec
Sequential Write 22 Mb/sec
Random Write 19 Mb/sec
Perc3/DC. Set up bios
Sequential Read 120 Mb/sec
Random Read 115 Mb/sec
Sequential Write 27 Mb/sec
Random Write 25 Mb/sec
Compaq Smart Array 6402
Sequential Read 228 Mb/sec
Random Read 161 Mb/sec
Sequential Write 47 Mb/sec
Random Write 36 Mb/sec
Shocking.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O%23fiU6crFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Increasing the CPU's is always a good way to deal with increased load but
> you need to do a few things first.
> Make sure all the code and tables are fully optimized. A bad query or
> lack of index can drag down a good server fast.
> Make sure you have enough ram to keep the relevant data in cache.
> Have a properly configured disk subsystem.
> Set the MAXDOP at the server level to less than the total number of procs
> to allow concurrent queries in peak times.
> --
> Andrew J. Kelly SQL MVP
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:1B595AAC-B34F-4414-8AF4-C6F664171AF0@.microsoft.com...
>> no I do not have preocessor queue issues as of now. Ours is a web
>> application
>> with lot of selects but few insert/updates (hotel reservations). The
>> reason I
>> am doing this is for future growth. The product has to be able to support
>> five times the current load. I am guessing increasing the load by 5 time
>> will
>> definately cause CPU queue issues. But Increasing the CPUs- is it a good
>> route to take to handle more load ? Any good reading material on this
>> subject.
>> --
>> RK
>>
>> "Andrew J. Kelly" wrote:
>> It has a lot to do with what it is you are doing and how you are doing
>> it.
>> Since that varies so much there is little a std calculation will
>> achieve.
>> With more CPU's you have the potential to handle more individual
>> transactions concurrently. You can also potentially do multi-threaded
>> operations faster such as DBREINDEX and CHECKDB etc. But that does not
>> guarantee it will. Do you have processor queue issues now?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "RK73" <RK73@.discussions.microsoft.com> wrote in message
>> news:B616692D-BB49-4038-9441-EE9F0756ED67@.microsoft.com...
>> > We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
>> > Enterprise.
>> >
>> > What will be the effect on SQL server performance if we add more CPUs
>> > ?
>> > Are there any calulations (or numbers available) we can do to
>> > determine
>> > the
>> > effect of additional CPUs on SQLl server performace ( no of threads,
>> > no of
>> > user connections etc)
>> > --
>> > RK
>>
>|||Thank you both your comments.
I have SQL Server 2k/ Windows 2003 EE on DELL PE 6850, 8 GB RAM, 4x3.33Ghz
hyperthreaded CPUs two DELL PERC 4/DC controllers. Curreently i am zooming
along. It is only for future I am worried about.
Thanks for the true RAID 10 tip.
--
RK
"Paul Cahill" wrote:
> What server do you have? We were running 500 users on a Dell 8450 with 4
> 900Mhz cpus.
> It failed a month ago and we had to quickly swap in a modern Compaq with 2 x
> 3.2ghz hyperthreading cpu's.
> The dell had 24 disks running raid 10 arrays in two split bus PV200s off two
> perc3/dc controllers.
> The compaq has only 6 disks but just wipes the floor with the old dell.
> While I've had the dell down I tried replacing the Dell Perc3's with the
> latest compaq controllers. Dispite the controllers being U320 they have to
> run U160 as the 15K rpm Fujitsu drives in the dell are a few years old. The
> sustained disk throughput increased by a factor of 2 both for reads and
> writes. I was never happy with the perc3's but now the compaq controllers
> proved the point. Note also that dell array manager does not set up raid 10
> correctly as a stripe of mirrors. It sets it up as a span of mirrors. You
> have to set up the raid 10 arrays through the dell controller bios to
> achieve true raid 10.
> Stats, all on a dell 8450, Windows 2003, Raid 10 on 8 disks over two
> channels (dual channel controllers). Write back cache enabled. all stats
> unbuffered by windows.
> Test tool Sisoft Sandra.
> Perc3/DC. Set up by array manager
> Sequential Read 33 Mb/sec
> Random Read 11 Mb/sec
> Sequential Write 22 Mb/sec
> Random Write 19 Mb/sec
> Perc3/DC. Set up bios
> Sequential Read 120 Mb/sec
> Random Read 115 Mb/sec
> Sequential Write 27 Mb/sec
> Random Write 25 Mb/sec
> Compaq Smart Array 6402
> Sequential Read 228 Mb/sec
> Random Read 161 Mb/sec
> Sequential Write 47 Mb/sec
> Random Write 36 Mb/sec
> Shocking.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O%23fiU6crFHA.2540@.TK2MSFTNGP09.phx.gbl...
> > Increasing the CPU's is always a good way to deal with increased load but
> > you need to do a few things first.
> >
> > Make sure all the code and tables are fully optimized. A bad query or
> > lack of index can drag down a good server fast.
> > Make sure you have enough ram to keep the relevant data in cache.
> > Have a properly configured disk subsystem.
> > Set the MAXDOP at the server level to less than the total number of procs
> > to allow concurrent queries in peak times.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "RK73" <RK73@.discussions.microsoft.com> wrote in message
> > news:1B595AAC-B34F-4414-8AF4-C6F664171AF0@.microsoft.com...
> >> no I do not have preocessor queue issues as of now. Ours is a web
> >> application
> >> with lot of selects but few insert/updates (hotel reservations). The
> >> reason I
> >> am doing this is for future growth. The product has to be able to support
> >> five times the current load. I am guessing increasing the load by 5 time
> >> will
> >> definately cause CPU queue issues. But Increasing the CPUs- is it a good
> >> route to take to handle more load ? Any good reading material on this
> >> subject.
> >>
> >> --
> >> RK
> >>
> >>
> >> "Andrew J. Kelly" wrote:
> >>
> >> It has a lot to do with what it is you are doing and how you are doing
> >> it.
> >> Since that varies so much there is little a std calculation will
> >> achieve.
> >> With more CPU's you have the potential to handle more individual
> >> transactions concurrently. You can also potentially do multi-threaded
> >> operations faster such as DBREINDEX and CHECKDB etc. But that does not
> >> guarantee it will. Do you have processor queue issues now?
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> >> news:B616692D-BB49-4038-9441-EE9F0756ED67@.microsoft.com...
> >> > We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
> >> > Enterprise.
> >> >
> >> > What will be the effect on SQL server performance if we add more CPUs
> >> > ?
> >> > Are there any calulations (or numbers available) we can do to
> >> > determine
> >> > the
> >> > effect of additional CPUs on SQLl server performace ( no of threads,
> >> > no of
> >> > user connections etc)
> >> > --
> >> > RK
> >>
> >>
> >>
> >
> >
>
>