Server Properties

[mike]

Help;
Im New with a company and the sql server is
maxing out the cpu's
We have 3 web servers load balanced....
large volume of data

the current Properties
Computer:
4 amd 2.88 processors
4 g Mememory

Recomendations of good sql setup
memory, cpu etc

Thank you
mike

[Erland Sommarskog]

Re: Server Properties

mike (vettes_n_jets@ yahoo.com) writes:

Im New with a company and the sql server is
maxing out the cpu's
We have 3 web servers load balanced....
large volume of data
>
the current Properties
Computer:
4 amd 2.88 processors
4 g Mememory
>
Recomendations of good sql setup
memory, cpu etc

While you can achieve some performance benefits by tuing the hardware
and its configuration, the major impact is usually in looking at queries
and how they are submitted.

For instance, a web app that submits all queries as fixed string with
input parameters interpolated, will take a far bigger toll on the server
in compilation and cache-lookup time than an app that uses parameterised
queries. Even more decisive is of course whether quereis are supported
by suitable indexes. And finally, good defragmentation rouines are really
helpful.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[mike]

Re: Server Properties

I know i must work on queries and other things
was just wondering what a good configuration is
at the server level.....

thanks for the information
mike

"Erland Sommarskog" <esquel@sommars kog.sewrote in message
news:Xns9892EE0 0675BEYazorman@ 127.0.0.1...

mike (vettes_n_jets@ yahoo.com) writes:

>Im New with a company and the sql server is
>maxing out the cpu's
>We have 3 web servers load balanced....
>large volume of data
>>
>the current Properties
>Computer:
>4 amd 2.88 processors
>4 g Mememory
>>
>Recomendatio ns of good sql setup
>memory, cpu etc

>
While you can achieve some performance benefits by tuing the hardware
and its configuration, the major impact is usually in looking at queries
and how they are submitted.
>
For instance, a web app that submits all queries as fixed string with
input parameters interpolated, will take a far bigger toll on the server
in compilation and cache-lookup time than an app that uses parameterised
queries. Even more decisive is of course whether quereis are supported
by suitable indexes. And finally, good defragmentation rouines are really
helpful.
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Ed Murphy]

Re: Server Properties

mike wrote:

I know i must work on queries and other things

Do you know that it should be your first priority? Non-optimized
queries often make a big difference.

was just wondering what a good configuration is
at the server level.....

Second priority is RAM. More RAM = more cached data = less frequent
disk access. (Disk access is a lot slower than RAM access.) Using
more than 4 GB requires a bit of non-trivial configuration.

Third priority is disk speed. Ideally, you want to keep data, logs,
temp database, and operating system on separate physical drives, or
at least separate logical ones. (Parallelism, scan patterns, and
fragmentation.) Higher RPM = faster disk access.

CPU should be adequate, but a typical SQL server is doing simple
operations on large volumes of data, thus bottlenecks somewhere else
(RAM cache, disk speed, network bandwidth) before it gets anywhere
close to bottlenecking on CPU. A bottleneck on CPU may indicate a
complex query in need of simplification (e.g. inefficient joins,
cursors that could be replaced with joins, sorting that could be
more effectively delegated to the client).

Network should be stable, and bandwidth should be adequate, but a
bottleneck on bandwidth may indicate a need for more filtering on the
data prior to transmitting it to the client.

[lucm]

Re: Server Properties

The most critical part of the server is the I/O (the hard disks).
However, if your CPUs are maxed out, then it might be useful to find
out if this is a general load issue or if there is a culprit (sp_who is
your friend!).

This being said, if you need a quick improvement and you don't have a
lot of time or skills to troubleshoot queries, here is what you could
do:

1) Open the Activity Monitor in the Management Studio (SQL 2005)

2) Find the Process ID that are using the most resources (CPU, Physical
I/O)

3) Right-click on the line of one of those Process ID and click on
Details. This should provide you with a query. Do this for a while in
order to collect a few queries that seem to occur often and pull a lot
of resources.

4) Open a New Query window in the Management studio and paste one of
the frequent queries.

5) Right-click in the query window and click on "Analyze query in
Database Engine Tuning Advisor"

6) Create the stats and indexes as suggested by the wizard (you can
even copy & paste the T-SQL from the wizard to create thoses indexes)

7) Repeat steps 4 to 6 for every queries you collected at step 3.

This is not a perfect solution, but it could lead to a serious
performance improvement while you get your feet wet with the queries
themselves.

Regards,
lucm

mike wrote:

I know i must work on queries and other things
was just wondering what a good configuration is
at the server level.....
>
thanks for the information
mike
>
"Erland Sommarskog" <esquel@sommars kog.sewrote in message
news:Xns9892EE0 0675BEYazorman@ 127.0.0.1...

mike (vettes_n_jets@ yahoo.com) writes:

Im New with a company and the sql server is
maxing out the cpu's
We have 3 web servers load balanced....
large volume of data
>
the current Properties
Computer:
4 amd 2.88 processors
4 g Mememory
>
Recomendations of good sql setup
memory, cpu etc

While you can achieve some performance benefits by tuing the hardware
and its configuration, the major impact is usually in looking at queries
and how they are submitted.

For instance, a web app that submits all queries as fixed string with
input parameters interpolated, will take a far bigger toll on the server
in compilation and cache-lookup time than an app that uses parameterised
queries. Even more decisive is of course whether quereis are supported
by suitable indexes. And finally, good defragmentation rouines are really
helpful.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[mike]

Re: Server Properties

Thank you Very Much Ed
Mike

"Ed Murphy" <emurphy42@soca l.rr.comwrote in message
news:XE4eh.6576 1$si3.7458@torn ado.socal.rr.co m...

mike wrote:
>

>I know i must work on queries and other things

>
Do you know that it should be your first priority? Non-optimized
queries often make a big difference.
>

>was just wondering what a good configuration is
>at the server level.....

>
Second priority is RAM. More RAM = more cached data = less frequent
disk access. (Disk access is a lot slower than RAM access.) Using
more than 4 GB requires a bit of non-trivial configuration.
>
Third priority is disk speed. Ideally, you want to keep data, logs,
temp database, and operating system on separate physical drives, or
at least separate logical ones. (Parallelism, scan patterns, and
fragmentation.) Higher RPM = faster disk access.
>
CPU should be adequate, but a typical SQL server is doing simple
operations on large volumes of data, thus bottlenecks somewhere else
(RAM cache, disk speed, network bandwidth) before it gets anywhere
close to bottlenecking on CPU. A bottleneck on CPU may indicate a
complex query in need of simplification (e.g. inefficient joins,
cursors that could be replaced with joins, sorting that could be
more effectively delegated to the client).
>
Network should be stable, and bandwidth should be adequate, but a
bottleneck on bandwidth may indicate a need for more filtering on the
data prior to transmitting it to the client.

[mike]

Re: Server Properties

very good information.... ..
very much appriciated Ed and Lucm

Thanks again
Mike

"mike" <vettes_n_jets@ yahoo.comwrote in message
news:uVZdh.3007 $Ga1.2849@newss vr12.news.prodi gy.net...

Help;
Im New with a company and the sql server is
maxing out the cpu's
We have 3 web servers load balanced....
large volume of data
>
the current Properties
Computer:
4 amd 2.88 processors
4 g Mememory
>
Recomendations of good sql setup
memory, cpu etc
>
Thank you
mike
>