Posted by Dan Guzman on 08/21/06 00:45

> 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@gmail.com> wrote in message
news:7P-dnTD_StlnP3XZ4p2dnA@telenor.com...
>* 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

[Back to original message]