|
|
Posted by lucm on 12/08/06 14:52
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@sommarskog.se> wrote in message
> news:Xns9892EE00675BEYazorman@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@sommarskog.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
[Back to original message]
|