EM - Current Activity

I manage a data warehouse. Each month I potentially update
millions of records (only if they've changed from the
previous month). You can imagine this update process is
quite time consuming. I've got the tables spread accross 2
to 3 drives via File Groups. The table I update from is
either a data type table variable, temp table or a table
on it's on drive with a clustered index. I've got 2 gigs
of RAM for SQL (Std Edition)but a total of 4, SQL's
priority is boosted, plenty of hard drive space, 4
processors. In EM's current activity, under Physical IO I
seem to have a bottleneck. My wait times for one process
(multiple instances) can easily be in the millions. When I
use Perf Mon to view disk activity (read\write) at least
one usually 2 drives are maxed out. Any thoughts on how I
can improve performance?Liz,
Well one way is ensure the log file is on a separate Raid 1 than anything
else. When you say you have the data spread across 3 drives do you mean 3
individual physical drives or 3 drive array's? 2 or 3 physical drives are
usually not enough to get good performance out of a typical DW application.
The more drives the better. For updates it is very important to have the
log on a separate array than the data. You can also try doing the updates
in smaller batches and see if that speeds things up but you will still get
high disk activity.
--
Andrew J. Kelly
SQL Server MVP
"Liz" <anonymous@.discussions.microsoft.com> wrote in message
news:257201c3af73$a15507b0$a601280a@.phx.gbl...
> I manage a data warehouse. Each month I potentially update
> millions of records (only if they've changed from the
> previous month). You can imagine this update process is
> quite time consuming. I've got the tables spread accross 2
> to 3 drives via File Groups. The table I update from is
> either a data type table variable, temp table or a table
> on it's on drive with a clustered index. I've got 2 gigs
> of RAM for SQL (Std Edition)but a total of 4, SQL's
> priority is boosted, plenty of hard drive space, 4
> processors. In EM's current activity, under Physical IO I
> seem to have a bottleneck. My wait times for one process
> (multiple instances) can easily be in the millions. When I
> use Perf Mon to view disk activity (read\write) at least
> one usually 2 drives are maxed out. Any thoughts on how I
> can improve performance?