DB Server Load

Hi,
My DB server is continously at a very high CPU usage. (80%-100%)
It is running a large scale web site with millions of users, so this is
expected, but recently we have been getting increased user traffic and
therefore increased database load.
We are planning on upgrading the database hardware, but I want to plan this
out correctly - what are the steps I should take to monitor where my
bottlenecks are, and where I would get the most benefit from an upgrade?
Also, how can I monitor which tables, queries, index, etc. are causing the
load on the DB server?
Thanks.Some additional info:
The DB server has 4 processors, and they are all at the same high levels.
(I'm watching them through Perfmon right now, all 4 are at 100% and stay
there for up to a minute at a time)|||It seems you already know that you have a CPU bottleneck, presumably by
using Windows performance monitor. The question is whether this is an
unavoidable consequence of your very heavy workload or something that you
can address with tuning and/or design. It is best to have as many CPUs
(cores) as possible for a high volume OLTP app.
I suggest you create a SQL trace so that you can identify the most
frequently run and costly queries. Start with only RPC completed and Batch
Completed events and specify a filter on duration and/or CPU. You can use
the SQL Profiler GUI to create the trace create script but don't use
Profiler to gather the data on your production box. Run the trace for a
representative sample interval (e.g. 15 minutes) and then stop the trace.
You can then import the data into a table using fn_trace_gettable for adhoc
analysis. Scrutinize the execution plans for those queries that are run
frequently or that consume the most CPU.
Also, consider client and/or middle tier caching to reduce the workload and
distribute the workload among multiple database servers, if possible.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gal Steinitz" <gal@.nospam.nospam> wrote in message
news:DFEF0FCB-1C78-4CFC-887B-D9CE68C15653@.microsoft.com...
> Hi,
> My DB server is continously at a very high CPU usage. (80%-100%)
> It is running a large scale web site with millions of users, so this is
> expected, but recently we have been getting increased user traffic and
> therefore increased database load.
> We are planning on upgrading the database hardware, but I want to plan
> this
> out correctly - what are the steps I should take to monitor where my
> bottlenecks are, and where I would get the most benefit from an upgrade?
> Also, how can I monitor which tables, queries, index, etc. are causing the
> load on the DB server?
> Thanks.|||Hi
Along with Dan's advice you may want to look at perfmon counters. For
example check out
http://www.sql-server-performance.com/ss_performance_monitoring.asp and
other articles relating to performance tuning on the site.
John
"Gal Steinitz" <gal@.nospam.nospam> wrote in message
news:DFEF0FCB-1C78-4CFC-887B-D9CE68C15653@.microsoft.com...
> Hi,
> My DB server is continously at a very high CPU usage. (80%-100%)
> It is running a large scale web site with millions of users, so this is
> expected, but recently we have been getting increased user traffic and
> therefore increased database load.
> We are planning on upgrading the database hardware, but I want to plan
> this
> out correctly - what are the steps I should take to monitor where my
> bottlenecks are, and where I would get the most benefit from an upgrade?
> Also, how can I monitor which tables, queries, index, etc. are causing the
> load on the DB server?
> Thanks.