sqlserver 2005: indexes on raid-0?

[boa]

I'm currently planning disk layouts and use for a new version of our
database. The current version has all data and indexes in the default
filegroup, placed on one big raid-5 array(6 drives) along with the
transaction log. Performance is not the best, as you may imagine...

Next week we will add another 14 drives and organize them in different
combos of raid-10 and raid-1, and then create several filegroups and
place tables and index data on separate physical drives.

Most of the database will be placed on a raid-10 array, and some parts
(tables/indexes/translog) will have their own raid-1 drives.

I've been playing with the rather incorrect idea of using raid-0 instead
of raid-1 on one or two of the new disk arrays we're adding and then
place (some) indexes on those drives.

The theory is that even if one drive fails, the db will stay up and it
will be easy to recreate the indexes when the disk has been replaced.
(We will have one hot spare available)

Does anyone know how well sqlserver 2005 handles disk loss in such a
configuration?

Any other comments? ;-)

boa

[rcamarda]

Boa:
I cant speak of what happens when an index disaapears because of a
drive failure.
However, I steer away from raid-5 as the have overhead for writes.
Worse, when a drive fails, it has to do N reads in order to reconstruct
the missing data. The more drives you have, the more reads it has to
do. (I has to read the data strip from each drive and read the checksum
to reconstruct the data from the missing drive).
I said all that to say you should see better performance from you raid
10 or 1 drive arrays.
Also, if you have more than one controller in the system to put logs
vs. data on the different controlers.
I've had two logical drives on a single controller what was an array.
In that situation I cant see splitting logs and data to get better
performance; they are using the same physical drives.
HTH
boa wrote:

I'm currently planning disk layouts and use for a new version of our
database. The current version has all data and indexes in the default
filegroup, placed on one big raid-5 array(6 drives) along with the
transaction log. Performance is not the best, as you may imagine...

Next week we will add another 14 drives and organize them in different
combos of raid-10 and raid-1, and then create several filegroups and
place tables and index data on separate physical drives.

Most of the database will be placed on a raid-10 array, and some parts
(tables/indexes/translog) will have their own raid-1 drives.

I've been playing with the rather incorrect idea of using raid-0 instead
of raid-1 on one or two of the new disk arrays we're adding and then
place (some) indexes on those drives.

The theory is that even if one drive fails, the db will stay up and it
will be easy to recreate the indexes when the disk has been replaced.
(We will have one hot spare available)

Does anyone know how well sqlserver 2005 handles disk loss in such a
configuration?

Any other comments? ;-)

boa

[boa]

* rcamarda wrote, On 20.08.2006 13:04:

Boa:
I cant speak of what happens when an index disaapears because of a
drive failure.
However, I steer away from raid-5 as the have overhead for writes.

I guess I was a bit unclear in the original post. The current version of
the database resides on a raid-5 array, the new/next version of our
database will be placed on a combination of raid-10 and raid-1 arrays.

So raid-5 is out, forever :-)

Boa

[snip]

[Dan Guzman]

The theory is that even if one drive fails, the db will stay up and it

will be easy to recreate the indexes when the disk has been replaced. (We
will have one hot spare available)

Does anyone know how well sqlserver 2005 handles disk loss in such a
configuration?

I've seen a SQL 2005 demo with files on removable drives that may provide
some insight. In that demo, the database remained online when USB drives
were physically removed as long as the transaction log was available.
Commands continued to run successfully when cached data were accessed and
even updates succeeded. However, commands that required physical I/O to
missing files (non-cached data) failed. The database was marked suspect
when SQL Server was restarted because recovery couldn't take place with
missing files.

You cannot easily recreate the indexes after you lose index files because a
full database restore is required after files are lost. You'll need to
restore from full backup and apply transaction log backups. However, at
least the database will be partially available during the degradation.

I suggest you stick with RAID-10 if high availability is important in your
environment. Disk storage is inexpensive nowadays.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"boa" <bo*****@gmail.comwrote in message
news:MZ********************@telenor.com...

I'm currently planning disk layouts and use for a new version of our
database. The current version has all data and indexes in the default
filegroup, placed on one big raid-5 array(6 drives) along with the
transaction log. Performance is not the best, as you may imagine...

Next week we will add another 14 drives and organize them in different
combos of raid-10 and raid-1, and then create several filegroups and place
tables and index data on separate physical drives.

Most of the database will be placed on a raid-10 array, and some parts
(tables/indexes/translog) will have their own raid-1 drives.

I've been playing with the rather incorrect idea of using raid-0 instead
of raid-1 on one or two of the new disk arrays we're adding and then place
(some) indexes on those drives.

The theory is that even if one drive fails, the db will stay up and it
will be easy to recreate the indexes when the disk has been replaced. (We
will have one hot spare available)

Does anyone know how well sqlserver 2005 handles disk loss in such a
configuration?

Any other comments? ;-)

boa

[boa]

* Dan Guzman wrote, On 20.08.2006 16:03:

>The theory is that even if one drive fails, the db will stay up and it
will be easy to recreate the indexes when the disk has been replaced. (We
will have one hot spare available)

Does anyone know how well sqlserver 2005 handles disk loss in such a
configuration?

I've seen a SQL 2005 demo with files on removable drives that may provide
some insight. In that demo, the database remained online when USB drives
were physically removed as long as the transaction log was available.
Commands continued to run successfully when cached data were accessed and
even updates succeeded. However, commands that required physical I/O to
missing files (non-cached data) failed. The database was marked suspect
when SQL Server was restarted because recovery couldn't take place with
missing files.

You cannot easily recreate the indexes after you lose index files because a
full database restore is required after files are lost. You'll need to
restore from full backup and apply transaction log backups. However, at
least the database will be partially available during the degradation.

Thanks, very interesting and exactly what I was looking/hoping for.

Can't see why the sqlserver needs a full restore just because it loses
some indexes, but if that's the way it is I'm not going to fight it...

>
I suggest you stick with RAID-10 if high availability is important in your
environment. Disk storage is inexpensive nowadays.

The disks themselves may be cheap, in our case it is a matter of
physical space, there's room for one disk rack only, that's it. So we're
throwing in a Dell PV1000 with room for 15 drives, no more.

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.

All in all we have 3-4 table groups, along with indexes, which are
heavily updated and inserted to, in addition to 60-80 "more quiet"
tables. In an ideal world one could have used 8 to 10 separate raid-10
arrays, but that's not possible with only 14 disks(15-1 hot spare).

So I plan to use 2 drives for the transaction log, 4 drives for a
raid-10 array, and then create 4 raid-1 arrays for index and table files
for some of the busiest tables.

Oh well, hopefully the new version will be fast enough even with RAID-1
and RAID-10, so then we can shut down and throw out some of the other
db-servers currently occupying our server rack. That's the plan, time
will show.

Thanks again.
Boa

[Dan Guzman]

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.

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.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"boa" <bo*****@gmail.comwrote in message
news:Na********************@telenor.com...

>* Dan Guzman wrote, On 20.08.2006 16:03:

>>The theory is that even if one drive fails, the db will stay up and it
will be easy to recreate the indexes when the disk has been replaced.
(We will have one hot spare available)

Does anyone know how well sqlserver 2005 handles disk loss in such a
configuration?

I've seen a SQL 2005 demo with files on removable drives that may provide
some insight. In that demo, the database remained online when USB drives
were physically removed as long as the transaction log was available.
Commands continued to run successfully when cached data were accessed and
even updates succeeded. However, commands that required physical I/O to
missing files (non-cached data) failed. The database was marked suspect
when SQL Server was restarted because recovery couldn't take place with
missing files.

You cannot easily recreate the indexes after you lose index files because
a full database restore is required after files are lost. You'll need to
restore from full backup and apply transaction log backups. However, at
least the database will be partially available during the degradation.

Thanks, very interesting and exactly what I was looking/hoping for.

Can't see why the sqlserver needs a full restore just because it loses
some indexes, but if that's the way it is I'm not going to fight it...

>>
I suggest you stick with RAID-10 if high availability is important in
your environment. Disk storage is inexpensive nowadays.

The disks themselves may be cheap, in our case it is a matter of physical
space, there's room for one disk rack only, that's it. So we're throwing
in a Dell PV1000 with room for 15 drives, no more.

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.

All in all we have 3-4 table groups, along with indexes, which are heavily
updated and inserted to, in addition to 60-80 "more quiet" tables. In an
ideal world one could have used 8 to 10 separate raid-10 arrays, but
that's not possible with only 14 disks(15-1 hot spare).

So I plan to use 2 drives for the transaction log, 4 drives for a raid-10
array, and then create 4 raid-1 arrays for index and table files for some
of the busiest tables.

Oh well, hopefully the new version will be fast enough even with RAID-1
and RAID-10, so then we can shut down and throw out some of the other
db-servers currently occupying our server rack. That's the plan, time will
show.

Thanks again.
Boa

[boa]

* 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

[Dan Guzman]

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" <bo*****@gmail.comwrote in message
news:7P********************@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

[Robert Klemme]

On 20.08.2006 19:59, boa wrote:

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.

Thanks for the interesting read! I'd like to play a bit devil's
advocate to improve my understanding here - feel free to correct me.
Now, I figure that if you physically separate your bulk loaded tables
from other tables you're reducing IO bandwidth for this set of tables
and all others (compared to distributing all data across all physical
drives) by basically reserving parts of that bandwidth for specific
data. This means that an import job with this configuration will take
longer because it has less IO bandwidth available. If pages for the
import job tables displace pages of other tables from the cache other
operations will be slowed down more than for the distributed case and
they have to wait *longer* for the import job to finish because of the
reduced IO bandwidth. So it seems to me that Dan's approach (spread all
data files evenly over available disk storage) is superior in this
situation because then the whole IO bandwidth is available all the time
for all operations. What do you think?

Kind regards

robert

[boa sema]

Robert Klemme wrote:

On 20.08.2006 19:59, boa wrote:

>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.

Thanks for the interesting read! I'd like to play a bit devil's
advocate to improve my understanding here - feel free to correct me.
Now, I figure that if you physically separate your bulk loaded tables
from other tables you're reducing IO bandwidth for this set of tables
and all others (compared to distributing all data across all physical
drives) by basically reserving parts of that bandwidth for specific
data. This means that an import job with this configuration will take
longer because it has less IO bandwidth available. If pages for the
import job tables displace pages of other tables from the cache other
operations will be slowed down more than for the distributed case and
they have to wait *longer* for the import job to finish because of the
reduced IO bandwidth. So it seems to me that Dan's approach (spread all
data files evenly over available disk storage) is superior in this
situation because then the whole IO bandwidth is available all the time
for all operations. What do you think?

I mostly agree with Dan. Let me first say that I don't know the correct
answer to this and what may be correct for one configuration may be
wrong for another. Anyway, here's my theory:

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

- 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.

- The rest of the database has lots of traffic while we're importing,
hundreds of SQL statements/second. The traffic is a mix of read and
write to/from the disks.

- The import job will finish faster on dedicated disks because of less
disk head movement caused by other traffic. Other traffic will finish
faster because of the same and because the import job "gets out of the
way" sooner.

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.

Do note that this is just a theory. Please prove me wrong, but please do
it before we put the solution in production ;-) I don't mind being wrong
at all, sometimes one can try to be too much of a smartass...

Boa

>
Kind regards

robert

[Robert Klemme]

On 21.08.2006 11:50, boa sema wrote:

Robert Klemme wrote:

>On 20.08.2006 19:59, boa wrote:

>>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.

Thanks for the interesting read! I'd like to play a bit devil's
advocate to improve my understanding here - feel free to correct me.
Now, I figure that if you physically separate your bulk loaded tables
from other tables you're reducing IO bandwidth for this set of tables
and all others (compared to distributing all data across all physical
drives) by basically reserving parts of that bandwidth for specific
data. This means that an import job with this configuration will take
longer because it has less IO bandwidth available. If pages for the
import job tables displace pages of other tables from the cache other
operations will be slowed down more than for the distributed case and
they have to wait *longer* for the import job to finish because of the
reduced IO bandwidth. So it seems to me that Dan's approach (spread
all data files evenly over available disk storage) is superior in this
situation because then the whole IO bandwidth is available all the
time for all operations. What do you think?

I mostly agree with Dan. Let me first say that I don't know the correct
answer to this and what may be correct for one configuration may be
wrong for another. Anyway, here's my theory:

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

Ack.

- 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.

- The rest of the database has lots of traffic while we're importing,
hundreds of SQL statements/second. The traffic is a mix of read and
write to/from the disks.

I guess you meant to say "read and write to/from *tables*". Whether
changes to tables result in physical IO depends on a whole lot of
conditions.

- The import job will finish faster on dedicated disks because of less
disk head movement caused by other traffic. Other traffic will finish
faster because of the same and because the import job "gets out of the
way" sooner.

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'll love to see Dan's answer on this one. I see these issues with your
theory:

- Disks with cache on them will optimize accesses to minimize latency
created by head moves.

- With a RAID since disk != physical disk there is not a single head
that moves and read and write requests are scheduled by the RAID
controller to optimize IO operations.

- Even SQL Server 2005 itself does optimize disk accesses. There is
some nice material in BOL:
http://msdn2.microsoft.com/en-us/library/ms191135.aspx
http://msdn2.microsoft.com/en-US/library/ms190761.aspx

Having said that it seems a little risky to me to base the theory on
disk head movements.

As I said earlier, to me it seems that you basically reserve IO
bandwidth for certain tasks and thus most IO bandwidth is unused most of
the time. This reminds me a bit of when packet switching was introduced
to networks: before that you needed a physical channel per connection
which gave reproducible throughput at the cost of added links. With
packet switching the whole bandwidth is available to all connections so
nothing is wasted.

Do note that this is just a theory. Please prove me wrong, but please do
it before we put the solution in production ;-) I don't mind being wrong
at all, sometimes one can try to be too much of a smartass...

Certainly. :-) As I said, I'm far from being an IO subsystem expert,
I'm just trying to apply some common sense and learn.

Kind regards

robert

[boa sema]

Thanks for replying so fast. Don't know about you guys, but I find
discussions/challenges like these very interesting. Not too many people
to discuss with where I am now, so thanks again.

Some things have changed since the original post. The guy responsible
for the import job has been working the entire weekend, found a couple
of bugs and has also suddenly decided that it is possible to run the job
at night instead of 24/7. This means that the theory probably will
remain a theory as the import doesn't block out the customers anymore. A
good thing!

Robert Klemme wrote:

On 21.08.2006 11:50, boa sema wrote:

>Robert Klemme wrote:

>>On 20.08.2006 19:59, boa wrote:

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.

Thanks for the interesting read! I'd like to play a bit devil's
advocate to improve my understanding here - feel free to correct me.
Now, I figure that if you physically separate your bulk loaded tables
from other tables you're reducing IO bandwidth for this set of tables
and all others (compared to distributing all data across all physical
drives) by basically reserving parts of that bandwidth for specific
data. This means that an import job with this configuration will
take longer because it has less IO bandwidth available. If pages for
the import job tables displace pages of other tables from the cache
other operations will be slowed down more than for the distributed
case and they have to wait *longer* for the import job to finish
because of the reduced IO bandwidth. So it seems to me that Dan's
approach (spread all data files evenly over available disk storage)
is superior in this situation because then the whole IO bandwidth is
available all the time for all operations. What do you think?

I mostly agree with Dan. Let me first say that I don't know the
correct answer to this and what may be correct for one configuration
may be wrong for another. Anyway, here's my theory:

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

Ack.

>- 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.

- The rest of the database has lots of traffic while we're importing,
hundreds of SQL statements/second. The traffic is a mix of read and
write to/from the disks.

I guess you meant to say "read and write to/from *tables*". Whether
changes to tables result in physical IO depends on a whole lot of
conditions.

Agreed.

>

>- The import job will finish faster on dedicated disks because of less
disk head movement caused by other traffic. Other traffic will finish
faster because of the same and because the import job "gets out of the
way" sooner.

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'll love to see Dan's answer on this one.

AOL ;-)

I see these issues with your
theory:

- Disks with cache on them will optimize accesses to minimize latency
created by head moves.

- With a RAID since disk != physical disk there is not a single head
that moves and read and write requests are scheduled by the RAID
controller to optimize IO operations.

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?

>
- Even SQL Server 2005 itself does optimize disk accesses. There is
some nice material in BOL:
http://msdn2.microsoft.com/en-us/library/ms191135.aspx
http://msdn2.microsoft.com/en-US/library/ms190761.aspx

Interesting read, thanks. Reminded me of how much I miss
http://www.amazon.com/gp/product/067...238242?ie=UTF8

(Once upon a time I actually implemented a file system for an SQL Server
6.5 clone, had too much spare time and decided that Linux needed a
proper RDBMS. This was back in '97, before Oracle and Sybase embraced
Linux. Even wrote a more or less complete SQL parser too, all in Lex,
Yacc and C. The only major change I made was to go for 8K pages instead
of 4K. Those were the days...)

>
Having said that it seems a little risky to me to base the theory on
disk head movements.

As I said earlier, to me it seems that you basically reserve IO
bandwidth for certain tasks and thus most IO bandwidth is unused most of
the time.

Correct. The aim was to get good, predicable performance all the time,
not maximum, but unpredictable performance most of the time. So I did
plan to 'waste' some IO bandwidth approx 66% of the time, but that's OK.

This reminds me a bit of when packet switching was introduced
to networks: before that you needed a physical channel per connection
which gave reproducible throughput at the cost of added links. With
packet switching the whole bandwidth is available to all connections so
nothing is wasted.

Yeah, but networks don't have seek time, do they? ;-)

Boa

[snip]

[Robert Klemme]

On 21.08.2006 14:26, boa sema wrote:

Thanks for replying so fast. Don't know about you guys, but I find
discussions/challenges like these very interesting. Not too many people
to discuss with where I am now, so thanks again.

Same here.

Some things have changed since the original post. The guy responsible
for the import job has been working the entire weekend, found a couple
of bugs and has also suddenly decided that it is possible to run the job
at night instead of 24/7. This means that the theory probably will
remain a theory as the import doesn't block out the customers anymore. A
good thing!

OTOH you cannot verify your theory and learn something which in itself
is a bad thing. :-) Also, your traffic might increase and you're back
to square 0.

Apart from that this is of course a good solution. Now you can even
skip your disk allocation and give the full IO bandwidth to *all*
processes. :-)

>I see these issues with your theory:

- Disks with cache on them will optimize accesses to minimize latency
created by head moves.

- With a RAID since disk != physical disk there is not a single head
that moves and read and write requests are scheduled by the RAID
controller to optimize IO operations.

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.

>As I said earlier, to me it seems that you basically reserve IO
bandwidth for certain tasks and thus most IO bandwidth is unused most
of the time.

Correct. The aim was to get good, predicable performance all the time,
not maximum, but unpredictable performance most of the time. So I did
plan to 'waste' some IO bandwidth approx 66% of the time, but that's OK.

The question is if it had worked out the way you planned. I don't know
how SQL Server schedules disk accesses for different sessions but in the
worst case your import would occupy the whole buffer and other sessions
would have to wait until the import has written all pages to disk via
the bandtwith restricted IO channel.

>This reminds me a bit of when packet switching was introduced to
networks: before that you needed a physical channel per connection
which gave reproducible throughput at the cost of added links. With
packet switching the whole bandwidth is available to all connections
so nothing is wasted.

Yeah, but networks don't have seek time, do they? ;-)

Um, yes. But they have latency, roundtrip times etc. and a SAN has
actually also seek times. :-)

Cheers

robert

[Dan Guzman]

- 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).

Similarly, if the workload is predictable enough so that you can isolate
sequential and random I/O activity (both reads and writes), this can also
improve disk efficiency.

- 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.

>
- 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.
The only advantage of isolating the objects is that the heads might not to
move as far, but that's probably a minor consideration.

- The rest of the database has lots of traffic while we're importing,
hundreds of SQL statements/second. The traffic is a mix of read and write
to/from the disks.

- The import job will finish faster on dedicated disks because of less
disk head movement caused by other traffic. Other traffic will finish
faster because of the same and because the import job "gets out of the
way" sooner.

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.

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.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"boa sema" <bo*****@gmail.comwrote in message
news:ec**********@bat-news01.banetele.basefarm.net...

Robert Klemme wrote:

>On 20.08.2006 19:59, boa wrote:

>>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.

Thanks for the interesting read! I'd like to play a bit devil's advocate
to improve my understanding here - feel free to correct me. Now, I figure
that if you physically separate your bulk loaded tables from other tables
you're reducing IO bandwidth for this set of tables and all others
(compared to distributing all data across all physical drives) by
basically reserving parts of that bandwidth for specific data. This
means that an import job with this configuration will take longer because
it has less IO bandwidth available. If pages for the import job tables
displace pages of other tables from the cache other operations will be
slowed down more than for the distributed case and they have to wait
*longer* for the import job to finish because of the reduced IO
bandwidth. So it seems to me that Dan's approach (spread all data files
evenly over available disk storage) is superior in this situation because
then the whole IO bandwidth is available all the time for all operations.
What do you think?

I mostly agree with Dan. Let me first say that I don't know the correct
answer to this and what may be correct for one configuration may be wrong
for another. Anyway, here's my theory:

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

- 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.

- The rest of the database has lots of traffic while we're importing,
hundreds of SQL statements/second. The traffic is a mix of read and write
to/from the disks.

- The import job will finish faster on dedicated disks because of less
disk head movement caused by other traffic. Other traffic will finish
faster because of the same and because the import job "gets out of the
way" sooner.

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.

Do note that this is just a theory. Please prove me wrong, but please do
it before we put the solution in production ;-) I don't mind being wrong
at all, sometimes one can try to be too much of a smartass...

Boa

>>
Kind regards

robert

[boa]

* Robert Klemme wrote, On 21.08.2006 14:01:

On 21.08.2006 14:26, boa sema wrote:

[snip]

>>I see these issues with your theory:

- Disks with cache on them will optimize accesses to minimize
latency created by head moves.

- With a RAID since disk != physical disk there is not a single head
that moves and read and write requests are scheduled by the RAID
controller to optimize IO operations.

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?

Boa

[snip]

[boa]

* 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]

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]

* 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