Re: sqlserver 2005: indexes on raid-0?
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 21 Aug 2006 00:45:33 GMT
The way the import jobs are written, they perform about 500.000 inserts
per 20 minutes, or 1.5" inserts per hour. Each insert is a separate
autocomitted transaction, IIRC. (Now recall that the db currently is
located on a raid-5 array(6 drives) and the transaction log is on the same
array and then add a couple of hundred other statements per second and
even some minor paging activities at times, plus 12 cpu-consuming and
memory loving .net clients AND a web server running on the same machine
AND hourly log backups to the same disk array, then imagine the great
performance we've had... )
I feel your pain. I suspect simply isolating the log will improve
performance dramatically. If you can somehow batch the import inserts so
that hundreds or thousands of rows are inserted per transaction, you will
probably get one or more orders of magnitude performance improvement. Not
only will this speed up the import process, overall response time will be
improved by relieving server stress .
--
Hope this helps.
Dan Guzman
SQL Server MVP
"boa" <boasema@xxxxxxxxx> wrote in message
news:7P-dnTD_StlnP3XZ4p2dnA@xxxxxxxxxxxxxx
* Dan Guzman wrote, On 20.08.2006 18:01:
Some tables/indexes are very busy, being updated every nth minute. To
stop that traffic from blocking reads/writes to other tables, I have
decided to place the busiest/largest tables on separate drives. Better
to have a little bit lower, but more predictable and consistent
performance than to place all tables/indexes on a massive raid-10 array,
IMO.
My philosophy is quite the opposite for a general purpose database
server. I usually segregate only transaction logs and spread all data
files evenly over available disk storage so that I/O is evenly
distributed over the I/O subsystem. Every environment is different but I
think it's often a mistake to deliberately create a disk hotspot. Keep
in mind that efficient caching
is paramount to performance. Slowing down writes to your busy
tables/indexes will result in more memory pressure and negatively affect
other applications.
No disagreement here, generally speaking. Our current situation is that
we, among other things, have very resource(disk) consuming import jobs
running 24/7. These jobs access the db approx. every third minute and then
do "other things" for the next 2 minutes. The exact load impact is not
even determined by us, but by external entities which we have no influence
over. At the same time we have other external customers accessing the
database both reading and writing.
The way the import jobs are written, they perform about 500.000 inserts
per 20 minutes, or 1.5" inserts per hour. Each insert is a separate
autocomitted transaction, IIRC. (Now recall that the db currently is
located on a raid-5 array(6 drives) and the transaction log is on the same
array and then add a couple of hundred other statements per second and
even some minor paging activities at times, plus 12 cpu-consuming and
memory loving .net clients AND a web server running on the same machine
AND hourly log backups to the same disk array, then imagine the great
performance we've had... )
Our situation right now is that the import job blocks all other traffic,
rendering the system close to unusable and the customers unhappy. The idea
is to move the import job related tables and indexes to separate drives to
reduce disk seek time during that 1 crucial minute(or 33% of the time).
This will hopefully improve overall throughput more than having everything
on one array.
There are other solutions available to us, one of them are to redesign the
database and rewrite the client applications. We're planning to do that
too, but that'll take weeks/months.
If performance is important to you, it might be worthwhile to run load
tests under the different disk configurations to see if isolating busy
tables/indexes is justified.
I hope to find the time to do just that, but with a Disk Write Queue
Length averaging *35* over long periods of time, we need to put the new
disks in production asap. I do plan to run two databases in parallel for
some time and do some tuning and error checking, and then do a final
migration at some point.
The new drives are hopefully best of breed, 15K RPM SAS drives. I expect
that just moving from 10K scsi raid-5 to 15K SAS raid-10 will be a big
boost, moving the transaction log to a separate drive even bigger. If we
in addition to that can remove traffic equaling 1.5" transactions/hour
from our main drives, we've bought ourselves enough time to rewrite the
client apps.
Boa
.
- References:
- sqlserver 2005: indexes on raid-0?
- From: boa
- Re: sqlserver 2005: indexes on raid-0?
- From: Dan Guzman
- Re: sqlserver 2005: indexes on raid-0?
- From: boa
- Re: sqlserver 2005: indexes on raid-0?
- From: Dan Guzman
- Re: sqlserver 2005: indexes on raid-0?
- From: boa
- sqlserver 2005: indexes on raid-0?
- Prev by Date: Re: Stored proc duplicating data
- Next by Date: Marketplace Announcment: Yellowfin Reporting Version 3 rollout
- Previous by thread: Re: sqlserver 2005: indexes on raid-0?
- Next by thread: Re: sqlserver 2005: indexes on raid-0?
- Index(es):
Relevant Pages
|
