SQL File I/O in the Storage Engine

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike H - 17 Nov 2006 17:44 GMT

The question is this, is there any "performance" advantage to creating
multiple files in a single file group in SQL 2000 or 2005?

It seems to be unclear so I'm curious if anyone has a definitive answer
from Microsoft on this. I've seen conflicting documentation, like
this:
http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx
which points to this:
http://www.microsoft.com/technet/prodtechnol/sql/70/reskit/part2/sqc01.mspx?mfr=true
which happens to be SQL Server 7 specific. As I understood the storage
engine got smarter in SQL 2000 and up so that thread management and
parallel I/O was more intelligent than being based on number of drive
letters.

The best support for not needing multiple files per file group comes
from this post I found:
http://groups.google.com/group/microsoft.public.sqlserver.server/msg/43ae64456de0344b
where andrew kelly says that there is an error in the SQL 2000 books
online and that he confirmed that multiple files are not necessary.

I just can't find anything from microsoft that states this. Anyone have
more information one way or the other?

Note the application I'm thinking of is an SQL Server accessing data
from a SAN that has 26 spindles in Raid 10 in it's one lun.

Reply to this Message

lucm - 17 Nov 2006 18:12 GMT

> Note the application I'm thinking of is an SQL Server accessing data
> from a SAN that has 26 spindles in Raid 10 in it's one lun.

I wonder, how many SP do you have on your San? For 26 disks my guess is
that you have 2 SP and 2 DAE.

In which case you could get a better performance with two LUNs, one for
the database (dedicated RAID-10 array of 24 disks) and the other for
the transaction logs (dedicated RAID-1 array of 2 disks), defaulting to
different SPs. Also make sure to split the RAID-1 disks over the two
DAE to get your San as fault-tolerant as possible.

Regards,
lucm

Reply to this Message

Mike H - 28 Nov 2006 18:47 GMT

> > Note the application I'm thinking of is an SQL Server accessing data
> > from a SAN that has 26 spindles in Raid 10 in it's one lun.
[quoted text clipped - 7 lines]
> different SPs. Also make sure to split the RAID-1 disks over the two
> DAE to get your San as fault-tolerant as possible.

Believe it or not, such would not be the case in the specific
application I discussed. Maximum I/O tests showed that the
architecture of the SAN allowed for higher I/O by using the more
spindles even when testing using SQL storage patterns for logs and
data. Now if we could have made two luns, each with 26 spindles, that
story may have been a bit different. But for the san I studied, 1 lun
of 26 drives in a raid 10 array had greater maximum performance for SQL
Server than 2 luns, splitting up the drives. (Note the SAN was fully
redundant.)

But more to the point of the question, I'm trying to understand if
there is any benefit in the current storage engine to creating mutliple
files for a specific file group regardless of the disk hardware
configuration. I.E., Is the storage engine only able to take advantage
of some feature that improves I/O specifically due to having multiple
files.?

Everything I've seen says no. Yet there are microsoft documents that
hint at YES, but don't specifically show this. EMC is saying yes,
without any thing to back it up but this SQL Server 7.0 document that
discusses the benefits.

Reply to this Message