What is more important here: 2 processors, 4 processors or more speed?  
Author Message
elgee





PostPosted: Wed Nov 19 02:02:05 CST 2003 Top

SQL Server >> What is more important here: 2 processors, 4 processors or more speed?

I would like to have your opinions on how to upgrade the server hardware
hosting a MS-SQL-Database
here:

Currently:
Windows 2000 Server SP2
Dual Intel Pentium III 1,2 GHz
2 GB RAM
Harddisks are on a SAN, so they are not needed to upgrade

MS-SQL Server 2000 Standard Edition, no other apps run on this machine

The SQL Server hosts a single database, size approx 3 GB, growing by about
10% per month. 60 highly concurrent users, in fact doing the same things at
the same time. The largest table has about 9.000.000 records, daily
increment 9000.

When the users start their tasks, I encounter - due to the intense
concurrency - performance degradation, although the overall performance is
ok. These peaks are beyond the servers capabilities. I monitored using the
performance monitor:
Processor Queue lenght: average 0,58 max 15
% Processor Time Processor 0 average 15,5 max 41,9 (but I have seen peaks up
to 90% as well)
% Processor Time Processor 1 average 19,5 max 61,1
% Privileged Time Processor 0 average 1,5 max 7,1
% Privileged Time Processor 1 average 1,3 max 5,7

I would recommend upgrading to a 4 processor system with Pentium 4 3,2 GHz

What´s your opinion?

Thanks for your input.

Bernd

SQL Server72  
 
 
Allan





PostPosted: Wed Nov 19 02:02:05 CST 2003 Top

SQL Server >> What is more important here: 2 processors, 4 processors or more speed? There doesn't look to be a right lot wrong with the processors. Sure the Q
length MAX is a little high but if that is a very short spike and not very
often then I do not think it matters that much.

I personally would be looking at the statements you are running and analyse
their execution plans to see what the optimiser is doing with them. When I
am optimising a DB Server I like to get an overall view of the server AND
the application performance before deciding which one is struggling.

Have a look at

http://www.sql-server-performance.com/articles_performance.asp

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org





> I would like to have your opinions on how to upgrade the server hardware
> hosting a MS-SQL-Database
> here:
>
> Currently:
> Windows 2000 Server SP2
> Dual Intel Pentium III 1,2 GHz
> 2 GB RAM
> Harddisks are on a SAN, so they are not needed to upgrade
>
> MS-SQL Server 2000 Standard Edition, no other apps run on this machine
>
> The SQL Server hosts a single database, size approx 3 GB, growing by about
> 10% per month. 60 highly concurrent users, in fact doing the same things
at
> the same time. The largest table has about 9.000.000 records, daily
> increment 9000.
>
> When the users start their tasks, I encounter - due to the intense
> concurrency - performance degradation, although the overall performance is
> ok. These peaks are beyond the servers capabilities. I monitored using the
> performance monitor:
> Processor Queue lenght: average 0,58 max 15
> % Processor Time Processor 0 average 15,5 max 41,9 (but I have seen peaks
up
> to 90% as well)
> % Processor Time Processor 1 average 19,5 max 61,1
> % Privileged Time Processor 0 average 1,5 max 7,1
> % Privileged Time Processor 1 average 1,3 max 5,7
>
> I would recommend upgrading to a 4 processor system with Pentium 4 3,2 GHz
>
> What´s your opinion?
>
> Thanks for your input.
>
> Bernd
>
>
>


 
 
Bernd





PostPosted: Wed Nov 19 03:57:25 CST 2003 Top

SQL Server >> What is more important here: 2 processors, 4 processors or more speed? Thanks, Allan, for your answer.

The average values are misledading, because the users are online between
6.00 am and 3.00pm only. So the peaks are reasonable and frequent and I
think, they need attention. I am after the app and the statements too, but I
am unsure how much effect an increase in speed and processors would have.

Thanks
Bernd



> There doesn't look to be a right lot wrong with the processors. Sure the
Q
> length MAX is a little high but if that is a very short spike and not very
> often then I do not think it matters that much.
>
> I personally would be looking at the statements you are running and
analyse
> their execution plans to see what the optimiser is doing with them. When
I
> am optimising a DB Server I like to get an overall view of the server AND
> the application performance before deciding which one is struggling.
>
> Have a look at
>
> http://www.sql-server-performance.com/articles_performance.asp
>
> --
>
> ----------------------------
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
>
>


> > I would like to have your opinions on how to upgrade the server hardware
> > hosting a MS-SQL-Database
> > here:
> >
> > Currently:
> > Windows 2000 Server SP2
> > Dual Intel Pentium III 1,2 GHz
> > 2 GB RAM
> > Harddisks are on a SAN, so they are not needed to upgrade
> >
> > MS-SQL Server 2000 Standard Edition, no other apps run on this machine
> >
> > The SQL Server hosts a single database, size approx 3 GB, growing by
about
> > 10% per month. 60 highly concurrent users, in fact doing the same things
> at
> > the same time. The largest table has about 9.000.000 records, daily
> > increment 9000.
> >
> > When the users start their tasks, I encounter - due to the intense
> > concurrency - performance degradation, although the overall performance
is
> > ok. These peaks are beyond the servers capabilities. I monitored using
the
> > performance monitor:
> > Processor Queue lenght: average 0,58 max 15
> > % Processor Time Processor 0 average 15,5 max 41,9 (but I have seen
peaks
> up
> > to 90% as well)
> > % Processor Time Processor 1 average 19,5 max 61,1
> > % Privileged Time Processor 0 average 1,5 max 7,1
> > % Privileged Time Processor 1 average 1,3 max 5,7
> >
> > I would recommend upgrading to a 4 processor system with Pentium 4 3,2
GHz
> >
> > What´s your opinion?
> >
> > Thanks for your input.
> >
> > Bernd
> >
> >
> >
>
>


 
 
Brian





PostPosted: Wed Nov 19 07:51:15 CST 2003 Top

SQL Server >> What is more important here: 2 processors, 4 processors or more speed? It's very difficult to know whether your current bottleneck is CPU, memory,
or IO without having more information.

For now... I would focus on the peak periods looking at averages when you're
worried about performance during small time deltas will be very
misleading....

also... I wouldn't assume you don't have an IO problems just becuase the
disks are on a SAN. There can STILL be contention on the SAN disks and you
could concievable have a trhoughput problem to the SAN itself caused by
driver or other problems.

--

Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com




> I would like to have your opinions on how to upgrade the server hardware
> hosting a MS-SQL-Database
> here:
>
> Currently:
> Windows 2000 Server SP2
> Dual Intel Pentium III 1,2 GHz
> 2 GB RAM
> Harddisks are on a SAN, so they are not needed to upgrade
>
> MS-SQL Server 2000 Standard Edition, no other apps run on this machine
>
> The SQL Server hosts a single database, size approx 3 GB, growing by about
> 10% per month. 60 highly concurrent users, in fact doing the same things
at
> the same time. The largest table has about 9.000.000 records, daily
> increment 9000.
>
> When the users start their tasks, I encounter - due to the intense
> concurrency - performance degradation, although the overall performance is
> ok. These peaks are beyond the servers capabilities. I monitored using the
> performance monitor:
> Processor Queue lenght: average 0,58 max 15
> % Processor Time Processor 0 average 15,5 max 41,9 (but I have seen peaks
up
> to 90% as well)
> % Processor Time Processor 1 average 19,5 max 61,1
> % Privileged Time Processor 0 average 1,5 max 7,1
> % Privileged Time Processor 1 average 1,3 max 5,7
>
> I would recommend upgrading to a 4 processor system with Pentium 4 3,2 GHz
>
> What´s your opinion?
>
> Thanks for your input.
>
> Bernd
>
>
>


 
 
Allan





PostPosted: Thu Nov 20 01:52:40 CST 2003 Top

SQL Server >> What is more important here: 2 processors, 4 processors or more speed? You do not provide any metrics for memory, Disk Q, Buffer Cache Hit Ratio
etc so there is little to go on. Looking purely at processors is a
difficult thing to do in determining if they are the bottleneck. Which SAN
are you using ? Have you montored the throughput using their tools/or your
own? Anything else on there?

Take a view of the system as a whole.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org





> Thanks, Allan, for your answer.
>
> The average values are misledading, because the users are online between
> 6.00 am and 3.00pm only. So the peaks are reasonable and frequent and I
> think, they need attention. I am after the app and the statements too, but
I
> am unsure how much effect an increase in speed and processors would have.
>
> Thanks
> Bernd
>


> > There doesn't look to be a right lot wrong with the processors. Sure
the
> Q
> > length MAX is a little high but if that is a very short spike and not
very
> > often then I do not think it matters that much.
> >
> > I personally would be looking at the statements you are running and
> analyse
> > their execution plans to see what the optimiser is doing with them.
When
> I
> > am optimising a DB Server I like to get an overall view of the server
AND
> > the application performance before deciding which one is struggling.
> >
> > Have a look at
> >
> > http://www.sql-server-performance.com/articles_performance.asp
> >
> > --
> >
> > ----------------------------
> > Allan Mitchell (Microsoft SQL Server MVP)
> > MCSE,MCDBA
> > www.SQLDTS.com
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> >
> >


> > > I would like to have your opinions on how to upgrade the server
hardware
> > > hosting a MS-SQL-Database
> > > here:
> > >
> > > Currently:
> > > Windows 2000 Server SP2
> > > Dual Intel Pentium III 1,2 GHz
> > > 2 GB RAM
> > > Harddisks are on a SAN, so they are not needed to upgrade
> > >
> > > MS-SQL Server 2000 Standard Edition, no other apps run on this machine
> > >
> > > The SQL Server hosts a single database, size approx 3 GB, growing by
> about
> > > 10% per month. 60 highly concurrent users, in fact doing the same
things
> > at
> > > the same time. The largest table has about 9.000.000 records, daily
> > > increment 9000.
> > >
> > > When the users start their tasks, I encounter - due to the intense
> > > concurrency - performance degradation, although the overall
performance
> is
> > > ok. These peaks are beyond the servers capabilities. I monitored using
> the
> > > performance monitor:
> > > Processor Queue lenght: average 0,58 max 15
> > > % Processor Time Processor 0 average 15,5 max 41,9 (but I have seen
> peaks
> > up
> > > to 90% as well)
> > > % Processor Time Processor 1 average 19,5 max 61,1
> > > % Privileged Time Processor 0 average 1,5 max 7,1
> > > % Privileged Time Processor 1 average 1,3 max 5,7
> > >
> > > I would recommend upgrading to a 4 processor system with Pentium 4 3,2
> GHz
> > >
> > > What´s your opinion?
> > >
> > > Thanks for your input.
> > >
> > > Bernd
> > >
> > >
> > >
> >
> >
>
>


 
 
Bernd





PostPosted: Thu Nov 20 03:23:24 CST 2003 Top

SQL Server >> What is more important here: 2 processors, 4 processors or more speed? Thanks for the answer.

> It's very difficult to know whether your current bottleneck is CPU,
memory,
> or IO without having more information.
I monitored the processors privileged time too, and it stays constantly
below 2-3% even when the % processor time itself goes up to 90%

> also... I wouldn't assume you don't have an IO problems just becuase the
> disks are on a SAN. There can STILL be contention on the SAN disks and you
> could concievable have a trhoughput problem to the SAN itself caused by
> driver or other problems.
Cash Hit ratio is up at 100%.

So - concerning hardware - what didi I miss?

Bernd


 
 
Bernd





PostPosted: Thu Nov 20 03:26:57 CST 2003 Top

SQL Server >> What is more important here: 2 processors, 4 processors or more speed? Thanks for the answer.

> You do not provide any metrics for memory, Disk Q, Buffer Cache Hit Ratio
Buffer Cache Hit ratio stays up at 100%, no paging occurs. Processor
priviliged time (as indicator for IO problems) is below 2-3% even when peaks
occur.

> etc so there is little to go on. Looking purely at processors is a
> difficult thing to do in determining if they are the bottleneck. Which
SAN
> are you using ? Have you montored the throughput using their tools/or
your
> own? Anything else on there?
Well, I can have a closer look at the SAN, maybe this is a good idea. I
though, if the server is already in trouble working on the processor queue,
waiting for the disks wont´s make things worse.

Bernd


 
 
Allan





PostPosted: Thu Nov 20 06:24:30 CST 2003 Top

SQL Server >> What is more important here: 2 processors, 4 processors or more speed? If you find no HW problems of note then have a look at your Queries. See
which ones are hogging the CPU, Disk R/W, Duration. Isolate them then have
a look at their execution plans.


--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org





> Thanks for the answer.
>
> > You do not provide any metrics for memory, Disk Q, Buffer Cache Hit
Ratio
> Buffer Cache Hit ratio stays up at 100%, no paging occurs. Processor
> priviliged time (as indicator for IO problems) is below 2-3% even when
peaks
> occur.
>
> > etc so there is little to go on. Looking purely at processors is a
> > difficult thing to do in determining if they are the bottleneck. Which
> SAN
> > are you using ? Have you montored the throughput using their tools/or
> your
> > own? Anything else on there?
> Well, I can have a closer look at the SAN, maybe this is a good idea. I
> though, if the server is already in trouble working on the processor
queue,
> waiting for the disks wont´s make things worse.
>
> Bernd
>
>