sqlserver 2005: indexes on raid-0?

[boa]

Re: sqlserver 2005: indexes on raid-0?

* Dan Guzman wrote, On 21.08.2006 14:03:

>- The slowest thing one can do with a disk is to move the heads(seek
>time), so avoiding unnecessary seek time will improve overall throughput.

>
This is true and exactly why it is so important to isolate logs on separate
disks. Logs are mostly sequential writes so you want to avoid other random
I/O from interfering and you want to avoid RAID-5 overhead. Importantly,
SQL Server waits for the physical write to the log to complete during COMMIT
so log writes are on the critical path. Write caching on the log can help
greatly but the controller must guarantee eventual write to disk (e.g.
battery backup).

We do plan to commit to the raid controller cache and trust the battery
backup.

[snip]

>- The import job writes lots of data to data and index pages on extents
>located on disk.

>
If data are imported in sequence, isolating that index can improve disk
efficiency. However, the other indexes will likely be mantained randomly so
it's probably best to place those on the same array(s) as other general
purpose I/O.

Good thinking, I forgot about that part.

>

>- All we do during import is inserting new rows in tables and the tables
>reside in their own files and the files reside on their own disks,
>(separate disks for table data and index data), so hopefully disk head
>movement will be minimal.
>>
>- The slowest thing one can do with a disk is to move the heads(seek
>time), so avoiding unnecessary seek time will improve overall throughput.
>>

>
Random I/O will likely result in some head movement so the heads are going
to move regardless of whether or not there are other objects on the drive.

You lost me here, why would the heads move? I assume that noone reads
from the tables during inserts.

[snip]

>What I aim for is less head movement and more parallell operations. This
>way the overall throughput will be better than if we only used a big
>RAID-10 array.

>
I hope you can find the time to do performance testing with the discussed
configurations. That's really the only way to tell for sure. I understand
that you are pressed to come up with a solution sooner than later so maybe
you can move logs as an interim step to buy some time do run the tests.

I'll probably not find the time. I could of course move just the tx log
and then test for a day or two, but I really hate using production
servers as test platforms. 'Childhood' experiences...

>
With all the discussion in this thread focusing on disk hardware, don't
forget the app side. If queries and indexes are poorly designed, you might
be able to get a dramatic improvement with a few strategic indexes or query
changes.
>

The app side is not forgotten and there's a lot to gain by changing
things. The problem is that it will take time, more time than we have.

Just a small thing like not commiting every insert will mean a lot as it
will reduce log traffic *a lot* and hopefully also other disk writes(if
more than one row of data is added to the page before it is flushed to disk)


Thanks
Boa

[Robert Klemme]

Re: sqlserver 2005: indexes on raid-0?

On 21.08.2006 16:43, boa wrote:

* Robert Klemme wrote, On 21.08.2006 14:01:

>>I agree that these issues are real, OTOH isn't "my theory" also the
>>reason one places the transaction logs on separate drives? If not,
>>what's the difference?

>>
>Access patterns are completely different for data files and TX log.
>In an OLTP environment TX log is continuously written and you need a
>RAID level that supports fast writing and high reliability. Data
>files are read and written with arbitrary (although optimized) access
>patterns.

>
I meant during import. I haven't read the sql server algorithm for page
and extent allocation when inserting new rows in a table, but assuming
that sql server will start adding rows at page 0,extent 0 (also assuming
proper clustering of table data and the order of the data inserted) and
then just go from there, the access pattern should be pretty similar,
shouldn't it?

Note that I made a *general* remark about OLTP systems. Yours seems to
be at least partly OLTP (the non insert stuff).

robert

[boa]

Re: sqlserver 2005: indexes on raid-0?

* Robert Klemme wrote, On 21.08.2006 16:14:

On 21.08.2006 16:43, boa wrote:

>* Robert Klemme wrote, On 21.08.2006 14:01:

>>>I agree that these issues are real, OTOH isn't "my theory" also the
>>>reason one places the transaction logs on separate drives? If not,
>>>what's the difference?
>>>
>>Access patterns are completely different for data files and TX log.
>>In an OLTP environment TX log is continuously written and you need a
>>RAID level that supports fast writing and high reliability. Data
>>files are read and written with arbitrary (although optimized) access
>>patterns.

>>
>I meant during import. I haven't read the sql server algorithm for
>page and extent allocation when inserting new rows in a table, but
>assuming that sql server will start adding rows at page 0,extent 0
>(also assuming proper clustering of table data and the order of the
>data inserted) and then just go from there, the access pattern should
>be pretty similar, shouldn't it?

>
Note that I made a *general* remark about OLTP systems. Yours seems to
be at least partly OLTP (the non insert stuff).
>

My mistake, I missed the *general* part of your remark. Sorry about
that. Yes, we're definitely OLTP for "the rest" of the db traffic so I
totally agree with you there.

Boa