Multiple files in FileGroup on RAID

We are putting out DBs on a RAID array and have the data and indexes in
their own file groups. My question is where can I find information about any
performance gain in I/O based on the number of files I have in the
FileGroup.
The array is made up of 14 drives with 1 hot spare (RAID 5). How many
files?
Thx"cw3" <cw@.3mc.com> wrote in message
news:%23yenkyNnDHA.3688@.TK2MSFTNGP11.phx.gbl...
> We are putting out DBs on a RAID array and have the data and indexes in
> their own file groups. My question is where can I find information about
any
> performance gain in I/O based on the number of files I have in the
> FileGroup.
> The array is made up of 14 drives with 1 hot spare (RAID 5). How many
> files?
>
Spreading out the data to multiple files in a filegroup on top of a single
RAID 5 array is redundant. Each file will be spread out over all 14 drives
by the RAID striping to begin with. It's useless do do it again with
multiple files per filegroup.
In your setup you needn't even bother putting data and indexes into seperate
filegroups.
If you had your disks RAIDed into 7 seperate 2 drive RAID 1 mirrors, then
you might spread a filegroup over multiple volumes for performance and
capacity.
But a better configuration might be to bundle the mirrored sets into 4-drive
stripe and mirror sets.
David|||While there can be performance gains with having multiple files per file
group there is no chart etc. that exists to show what your after. The gains
are going to be greatest with lots of drive arrays and lots of processors
when you have multiple files. Chances are you will not max out the
capabilities of the way you have it now with that hardware setup.
--
Andrew J. Kelly
SQL Server MVP
"cw3" <cw@.3mc.com> wrote in message
news:%23yenkyNnDHA.3688@.TK2MSFTNGP11.phx.gbl...
> We are putting out DBs on a RAID array and have the data and indexes in
> their own file groups. My question is where can I find information about
any
> performance gain in I/O based on the number of files I have in the
> FileGroup.
> The array is made up of 14 drives with 1 hot spare (RAID 5). How many
> files?
> Thx
>

Multiple Filegroups.

Hi Everyone,
I added a secondary filegroup to a production database and moved some tables
to it from the Primary Filegroup.In the process I changed the database
recovery model to Simple and back to Full,backed up the Transaction Log and
shrunk it,shrunk the primary filegroup datafile. The database recovery model
is Full at the moment. I have noticed 2 oddities:
1. The Transaction Log appears to grow much slower than before(only 2 - 5 MB
a day,the database is being used as usual)
2. sp_spaceused and Enterprise Manager Console report strange values for
database available space:
db_name size unallocated_space
DB_LIVE 55812.06 MB -4257.63 MB
reserved data index_size unused
61507528 KB 61203424 KB 291632 KB 12472 KB
Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE' or
is there somethig else I have to do?
Best Regards.I have another question. I took a Full Backup of the database before adding
the secondary filegroup(single filegroup database) , the backup file size is
about 55,4 GB.
Today I backed up the Primary Filegroup and the newly added Filegroup1. The
backup file size for the Primary filegroup is about 45 GB and backup file
size for the Filegroup1 filegroup is about 6 GB and that adds up to 51 GB.
As far as I know no data has been deleted. Is there any explanation
regarding the cause of the total size reduction ?
Regards.
Sezgin Rafet
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> Hi Everyone,
> I added a secondary filegroup to a production database and moved some
> tables to it from the Primary Filegroup.In the process I changed the
> database recovery model to Simple and back to Full,backed up the
> Transaction Log and shrunk it,shrunk the primary filegroup datafile. The
> database recovery model is Full at the moment. I have noticed 2 oddities:
> 1. The Transaction Log appears to grow much slower than before(only 2 - 5
> MB a day,the database is being used as usual)
> 2. sp_spaceused and Enterprise Manager Console report strange values for
> database available space:
> db_name size unallocated_space
> DB_LIVE 55812.06 MB -4257.63 MB
> reserved data index_size
> unused
> 61507528 KB 61203424 KB 291632 KB 12472 KB
>
> Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
> or is there somethig else I have to do?
> Best Regards.
>|||Hi
You don't say how you moved the tables, I assumed that you re-created the
clustered index in the new filegroup, in which case it would have been
re-built. If you had not defragged the index for a while then this may be
where the space has been found. You may want to look at what fill factors yo
u
are using or whether the clustered index is suitable.
John
"Sezgin Rafet" wrote:

> I have another question. I took a Full Backup of the database before addin
g
> the secondary filegroup(single filegroup database) , the backup file size
is
> about 55,4 GB.
> Today I backed up the Primary Filegroup and the newly added Filegroup1. Th
e
> backup file size for the Primary filegroup is about 45 GB and backup file
> size for the Filegroup1 filegroup is about 6 GB and that adds up to 51 GB.
> As far as I know no data has been deleted. Is there any explanation
> regarding the cause of the total size reduction ?
> Regards.
> Sezgin Rafet
>
> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
>
>|||Hello John,
Thanks for the reply.
I moved the tables using Enterprise Manager -> Design Table -> Properties ->
Selected Filegroup1 as the Table Filegroup -> Close -> Save
All the tables I moved this way have the same clustered index(the tables
have the same structuce,each table holds 1 month's worth of data) , so I
presume the index was rebuit for each table(my knowledge about indexing is
limited).We don't do anything regarding index defragmentation , thanks to
you it is only now that I realize it is an important issue to us.
In the beginning of each month a new data table is created(programmaticaly
by a custom Windows Service).
Data is fed into the table at very high rate from a SCADA system. Older data
tables are not modified.
Last month's data table has about 70 000 000 rows and is about 6.3 GB in
size.
As each new data table is created I will move the oldest data table to the
secondary filegroup.
I intend to keep 2 data tables in the Primary Filegroup(it is the Default
Filegroup as well).
Clustered Index Fill Factor is 0 % - is this a good choice ?.
What would be the best method to defragment the clustered index ? (This may
be no longer necessary , because when moving the tables it will be rebilt.
Any data table will be having data changes for only a period of 1 month)
What would be the advantage of using a non-clustered index in our case ,
would the query performance loss be acceptable ?
Best Regards.
Sezgin
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:86C16903-A902-4706-951C-D5E25E7C68C2@.microsoft.com...[vbcol=seagreen]
> Hi
> You don't say how you moved the tables, I assumed that you re-created the
> clustered index in the new filegroup, in which case it would have been
> re-built. If you had not defragged the index for a while then this may be
> where the space has been found. You may want to look at what fill factors
> you
> are using or whether the clustered index is suitable.
> John
> "Sezgin Rafet" wrote:
>|||Hi
I don't know what commands EM uses to move tables, but at a guess it creates
a new table, sucks the data into from the old table and then renames the new
table after dropping it. This would mean that any clustered index would need
to be in the same filegroup. Without knowing what your indexes are it is har
d
to comment on whether the fill factor is good or whether you would benefit
from a different from a different clustered index or even if your system
would work better without one (if speed of inserts is more important than
speed of retrieval).
You may want to consider changing your processing to load into a "standard"
set of tables, then do a monthly archive process into a new table (which is
basically what I think EM does for you). This could be automated and you the
n
have the choice of having different indexes on the load table to the archive
d
data; you would also be able to build the indexes on the archived data at th
e
end of the load process so that they would be fully optimized and if no data
changes are made they could have 100% fill factor. If this data is rarely
accessed you may even consider archiving it to a separate database.
For defragging look at the topic DBCC SHOWCONTIG in Books Online and there
is an example script you can use as a starting point for any manual
maintenance task. You can use DBCC DBREINDEX instead of DBCC INDEXDEFRAG if
you want a defragment the whole index. Alternatively look at the database
maintenance wizard and set up a job which will create a maintenance plan for
your database(s).
HTH
John
"Sezgin Rafet" wrote:

> Hello John,
> Thanks for the reply.
> I moved the tables using Enterprise Manager -> Design Table -> Properties
->
> Selected Filegroup1 as the Table Filegroup -> Close -> Save
> All the tables I moved this way have the same clustered index(the tables
> have the same structuce,each table holds 1 month's worth of data) , so I
> presume the index was rebuit for each table(my knowledge about indexing is
> limited).We don't do anything regarding index defragmentation , thanks to
> you it is only now that I realize it is an important issue to us.
> In the beginning of each month a new data table is created(programmaticaly
> by a custom Windows Service).
> Data is fed into the table at very high rate from a SCADA system. Older da
ta
> tables are not modified.
> Last month's data table has about 70 000 000 rows and is about 6.3 GB in
> size.
> As each new data table is created I will move the oldest data table to the
> secondary filegroup.
> I intend to keep 2 data tables in the Primary Filegroup(it is the Default
> Filegroup as well).
> Clustered Index Fill Factor is 0 % - is this a good choice ?.
> What would be the best method to defragment the clustered index ? (This ma
y
> be no longer necessary , because when moving the tables it will be rebilt.
> Any data table will be having data changes for only a period of 1 month)
> What would be the advantage of using a non-clustered index in our case ,
> would the query performance loss be acceptable ?
> Best Regards.
>
> Sezgin
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:86C16903-A902-4706-951C-D5E25E7C68C2@.microsoft.com...
>
>

Multiple Filegroups.

Hi Everyone,
I added a secondary filegroup to a production database and moved some tables
to it from the Primary Filegroup.In the process I changed the database
recovery model to Simple and back to Full,backed up the Transaction Log and
shrunk it,shrunk the primary filegroup datafile. The database recovery model
is Full at the moment. I have noticed 2 oddities:
1. The Transaction Log appears to grow much slower than before(only 2 - 5 MB
a day,the database is being used as usual)
2. sp_spaceused and Enterprise Manager Console report strange values for
database available space:
db_name size unallocated_space
DB_LIVE 55812.06 MB -4257.63 MB
reserved data index_size unused
61507528 KB 61203424 KB 291632 KB 12472 KB
Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE' or
is there somethig else I have to do?
Best Regards.I have another question. I took a Full Backup of the database before adding
the secondary filegroup(single filegroup database) , the backup file size is
about 55,4 GB.
Today I backed up the Primary Filegroup and the newly added Filegroup1. The
backup file size for the Primary filegroup is about 45 GB and backup file
size for the Filegroup1 filegroup is about 6 GB and that adds up to 51 GB.
As far as I know no data has been deleted. Is there any explanation
regarding the cause of the total size reduction ?
Regards.
Sezgin Rafet
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> Hi Everyone,
> I added a secondary filegroup to a production database and moved some
> tables to it from the Primary Filegroup.In the process I changed the
> database recovery model to Simple and back to Full,backed up the
> Transaction Log and shrunk it,shrunk the primary filegroup datafile. The
> database recovery model is Full at the moment. I have noticed 2 oddities:
> 1. The Transaction Log appears to grow much slower than before(only 2 - 5
> MB a day,the database is being used as usual)
> 2. sp_spaceused and Enterprise Manager Console report strange values for
> database available space:
> db_name size unallocated_space
> DB_LIVE 55812.06 MB -4257.63 MB
> reserved data index_size
> unused
> 61507528 KB 61203424 KB 291632 KB 12472 KB
>
> Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
> or is there somethig else I have to do?
> Best Regards.
>|||Hi
You don't say how you moved the tables, I assumed that you re-created the
clustered index in the new filegroup, in which case it would have been
re-built. If you had not defragged the index for a while then this may be
where the space has been found. You may want to look at what fill factors you
are using or whether the clustered index is suitable.
John
"Sezgin Rafet" wrote:
> I have another question. I took a Full Backup of the database before adding
> the secondary filegroup(single filegroup database) , the backup file size is
> about 55,4 GB.
> Today I backed up the Primary Filegroup and the newly added Filegroup1. The
> backup file size for the Primary filegroup is about 45 GB and backup file
> size for the Filegroup1 filegroup is about 6 GB and that adds up to 51 GB.
> As far as I know no data has been deleted. Is there any explanation
> regarding the cause of the total size reduction ?
> Regards.
> Sezgin Rafet
>
> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> > Hi Everyone,
> >
> > I added a secondary filegroup to a production database and moved some
> > tables to it from the Primary Filegroup.In the process I changed the
> > database recovery model to Simple and back to Full,backed up the
> > Transaction Log and shrunk it,shrunk the primary filegroup datafile. The
> > database recovery model is Full at the moment. I have noticed 2 oddities:
> >
> > 1. The Transaction Log appears to grow much slower than before(only 2 - 5
> > MB a day,the database is being used as usual)
> >
> > 2. sp_spaceused and Enterprise Manager Console report strange values for
> > database available space:
> >
> > db_name size unallocated_space
> > DB_LIVE 55812.06 MB -4257.63 MB
> >
> > reserved data index_size
> > unused
> > 61507528 KB 61203424 KB 291632 KB 12472 KB
> >
> >
> > Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
> > or is there somethig else I have to do?
> >
> > Best Regards.
> >
> >
>
>|||Hello John,
Thanks for the reply.
I moved the tables using Enterprise Manager -> Design Table -> Properties ->
Selected Filegroup1 as the Table Filegroup -> Close -> Save
All the tables I moved this way have the same clustered index(the tables
have the same structuce,each table holds 1 month's worth of data) , so I
presume the index was rebuit for each table(my knowledge about indexing is
limited).We don't do anything regarding index defragmentation , thanks to
you it is only now that I realize it is an important issue to us.
In the beginning of each month a new data table is created(programmaticaly
by a custom Windows Service).
Data is fed into the table at very high rate from a SCADA system. Older data
tables are not modified.
Last month's data table has about 70 000 000 rows and is about 6.3 GB in
size.
As each new data table is created I will move the oldest data table to the
secondary filegroup.
I intend to keep 2 data tables in the Primary Filegroup(it is the Default
Filegroup as well).
Clustered Index Fill Factor is 0 % - is this a good choice ?.
What would be the best method to defragment the clustered index ? (This may
be no longer necessary , because when moving the tables it will be rebilt.
Any data table will be having data changes for only a period of 1 month)
What would be the advantage of using a non-clustered index in our case ,
would the query performance loss be acceptable ?
Best Regards.
Sezgin
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:86C16903-A902-4706-951C-D5E25E7C68C2@.microsoft.com...
> Hi
> You don't say how you moved the tables, I assumed that you re-created the
> clustered index in the new filegroup, in which case it would have been
> re-built. If you had not defragged the index for a while then this may be
> where the space has been found. You may want to look at what fill factors
> you
> are using or whether the clustered index is suitable.
> John
> "Sezgin Rafet" wrote:
>> I have another question. I took a Full Backup of the database before
>> adding
>> the secondary filegroup(single filegroup database) , the backup file size
>> is
>> about 55,4 GB.
>> Today I backed up the Primary Filegroup and the newly added Filegroup1.
>> The
>> backup file size for the Primary filegroup is about 45 GB and backup file
>> size for the Filegroup1 filegroup is about 6 GB and that adds up to 51
>> GB.
>> As far as I know no data has been deleted. Is there any explanation
>> regarding the cause of the total size reduction ?
>> Regards.
>> Sezgin Rafet
>>
>> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
>> news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
>> > Hi Everyone,
>> >
>> > I added a secondary filegroup to a production database and moved some
>> > tables to it from the Primary Filegroup.In the process I changed the
>> > database recovery model to Simple and back to Full,backed up the
>> > Transaction Log and shrunk it,shrunk the primary filegroup datafile.
>> > The
>> > database recovery model is Full at the moment. I have noticed 2
>> > oddities:
>> >
>> > 1. The Transaction Log appears to grow much slower than before(only 2 -
>> > 5
>> > MB a day,the database is being used as usual)
>> >
>> > 2. sp_spaceused and Enterprise Manager Console report strange values
>> > for
>> > database available space:
>> >
>> > db_name size unallocated_space
>> > DB_LIVE 55812.06 MB -4257.63 MB
>> >
>> > reserved data index_size
>> > unused
>> > 61507528 KB 61203424 KB 291632 KB 12472 KB
>> >
>> >
>> > Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
>> > or is there somethig else I have to do?
>> >
>> > Best Regards.
>> >
>> >
>>|||Hi
I don't know what commands EM uses to move tables, but at a guess it creates
a new table, sucks the data into from the old table and then renames the new
table after dropping it. This would mean that any clustered index would need
to be in the same filegroup. Without knowing what your indexes are it is hard
to comment on whether the fill factor is good or whether you would benefit
from a different from a different clustered index or even if your system
would work better without one (if speed of inserts is more important than
speed of retrieval).
You may want to consider changing your processing to load into a "standard"
set of tables, then do a monthly archive process into a new table (which is
basically what I think EM does for you). This could be automated and you then
have the choice of having different indexes on the load table to the archived
data; you would also be able to build the indexes on the archived data at the
end of the load process so that they would be fully optimized and if no data
changes are made they could have 100% fill factor. If this data is rarely
accessed you may even consider archiving it to a separate database.
For defragging look at the topic DBCC SHOWCONTIG in Books Online and there
is an example script you can use as a starting point for any manual
maintenance task. You can use DBCC DBREINDEX instead of DBCC INDEXDEFRAG if
you want a defragment the whole index. Alternatively look at the database
maintenance wizard and set up a job which will create a maintenance plan for
your database(s).
HTH
John
"Sezgin Rafet" wrote:
> Hello John,
> Thanks for the reply.
> I moved the tables using Enterprise Manager -> Design Table -> Properties ->
> Selected Filegroup1 as the Table Filegroup -> Close -> Save
> All the tables I moved this way have the same clustered index(the tables
> have the same structuce,each table holds 1 month's worth of data) , so I
> presume the index was rebuit for each table(my knowledge about indexing is
> limited).We don't do anything regarding index defragmentation , thanks to
> you it is only now that I realize it is an important issue to us.
> In the beginning of each month a new data table is created(programmaticaly
> by a custom Windows Service).
> Data is fed into the table at very high rate from a SCADA system. Older data
> tables are not modified.
> Last month's data table has about 70 000 000 rows and is about 6.3 GB in
> size.
> As each new data table is created I will move the oldest data table to the
> secondary filegroup.
> I intend to keep 2 data tables in the Primary Filegroup(it is the Default
> Filegroup as well).
> Clustered Index Fill Factor is 0 % - is this a good choice ?.
> What would be the best method to defragment the clustered index ? (This may
> be no longer necessary , because when moving the tables it will be rebilt.
> Any data table will be having data changes for only a period of 1 month)
> What would be the advantage of using a non-clustered index in our case ,
> would the query performance loss be acceptable ?
> Best Regards.
>
> Sezgin
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:86C16903-A902-4706-951C-D5E25E7C68C2@.microsoft.com...
> > Hi
> >
> > You don't say how you moved the tables, I assumed that you re-created the
> > clustered index in the new filegroup, in which case it would have been
> > re-built. If you had not defragged the index for a while then this may be
> > where the space has been found. You may want to look at what fill factors
> > you
> > are using or whether the clustered index is suitable.
> >
> > John
> >
> > "Sezgin Rafet" wrote:
> >
> >> I have another question. I took a Full Backup of the database before
> >> adding
> >> the secondary filegroup(single filegroup database) , the backup file size
> >> is
> >> about 55,4 GB.
> >> Today I backed up the Primary Filegroup and the newly added Filegroup1.
> >> The
> >> backup file size for the Primary filegroup is about 45 GB and backup file
> >> size for the Filegroup1 filegroup is about 6 GB and that adds up to 51
> >> GB.
> >> As far as I know no data has been deleted. Is there any explanation
> >> regarding the cause of the total size reduction ?
> >>
> >> Regards.
> >>
> >> Sezgin Rafet
> >>
> >>
> >>
> >> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> >> news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> >> > Hi Everyone,
> >> >
> >> > I added a secondary filegroup to a production database and moved some
> >> > tables to it from the Primary Filegroup.In the process I changed the
> >> > database recovery model to Simple and back to Full,backed up the
> >> > Transaction Log and shrunk it,shrunk the primary filegroup datafile.
> >> > The
> >> > database recovery model is Full at the moment. I have noticed 2
> >> > oddities:
> >> >
> >> > 1. The Transaction Log appears to grow much slower than before(only 2 -
> >> > 5
> >> > MB a day,the database is being used as usual)
> >> >
> >> > 2. sp_spaceused and Enterprise Manager Console report strange values
> >> > for
> >> > database available space:
> >> >
> >> > db_name size unallocated_space
> >> > DB_LIVE 55812.06 MB -4257.63 MB
> >> >
> >> > reserved data index_size
> >> > unused
> >> > 61507528 KB 61203424 KB 291632 KB 12472 KB
> >> >
> >> >
> >> > Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
> >> > or is there somethig else I have to do?
> >> >
> >> > Best Regards.
> >> >
> >> >
> >>
> >>
> >>
>
>

Multiple data files in a filegroup

I have a storage subsystem which allows five independant RAID 10 devices. I am creating five filegroups each with 2 data files distributed across the devices in a smart round robin fashion.

I have assigned tables which are commonly joined between the filegroups. For example, Table A is in Filegroup 1 while Table B is in Filegroup 2. The most common and high volume query is the join between these two tables.

My question is this: I understand SQL Server is theaded by data file, not filegroup. But for filegroups which have 2 or more data files, do they both grow evenly? Or once one data file is full, SQL Server then writes all new data to the new data file in the same filegroup.

Hopefully this is not too confusing.

Thanks.

BryanSQL Server will write data to the members of a filegroup evenly, so if you create 2 100MB files in a filegroup, then insert 150MB of data into this filegroup, you will have 75 MB in each. Figuring out what you have after you delete 80 MB of data is virtually impossible, however. Hope this helps.

Multiple Data Files and paralell IO - yes or no

I've looked at many of the threads in this group that discuss whether
or not multiple data files in a filegroup will improve IO performance.
Some say that the BOL is wrong and that you don't need additional
files to have SQL server use parallel IO (starting with SQL 2K).
Others say that it still helps even in SQL 2K. I'm planning on doing
some benchmarking, but thought I'd throw it out there one more time.
What is Microsoft's stance on this? Should we or should we not use
multiple files in a filegroup. If we should, do we use the rule of
thumb of roughly 1 file per spindle in the RAID?
Thanks!
-Peter"Peter Daniels" <nospampedro@.yahoo.com> wrote in message
news:2fd8f155.0403291246.190893b6@.posting.google.com...
> I've looked at many of the threads in this group that discuss whether
> or not multiple data files in a filegroup will improve IO performance.
> Some say that the BOL is wrong and that you don't need additional
> files to have SQL server use parallel IO (starting with SQL 2K).
> Others say that it still helps even in SQL 2K. I'm planning on doing
> some benchmarking, but thought I'd throw it out there one more time.
> What is Microsoft's stance on this? Should we or should we not use
> multiple files in a filegroup. If we should, do we use the rule of
> thumb of roughly 1 file per spindle in the RAID?
>
Multiple files per filegroup and RAID can each be used to achieve parallel
IO.
If you have say, 10 disks in 5 2-disk raid mirrors, you might let a
filegroup span 2 of the mirror sets to to improve IO. But just as easily,
you could join the 2 mirror sets into a single stripe set and a single
logical volume. Then you can just use a single file to get parallel IO.
Makes your head hurt.
Since you can usually achieve the same thing with RAID alone that you could
achieve with RAID+Multiple files per filegroup, I would use RAID to alter
the IO performance profile, and always use a single file per filegroup.
And for OLTP applications just get as many disks as you can and SAME (Stripe
and Mirror Everything.
If you have
1 no raid
2 mirror
4 2 mirror sets
6 1 striped mirror set, one mirror set
8 2 striped mirror sets
...
David
David|||Peter,
Microsoft's stance on this is that there are too many variables to give a st
ock answer that will work for all hardware configurations. In my experience
using differeing hardware, I have got the best performance and ease of admin
istration by having one fil
e per database. However, you will need to do your own benchmarking on your o
wn hardware to be confident that the decision you make is right for your env
ironment.
Unless your databases are hundreds of gigabytes, I would suspect that one fi
le per database will be all you need.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||The last answer I got, from someone working for Microsoft Consulting
Services, was that there was no benefit to multiple files on the same disk
volume. However if you have multiple volumes you MAY see improvements.
It depends on the IO characteristics of your disk volumes. Example, if you
had 20 spindles would it be better to create one large RAID volume or >1
smaller volumes if they're all on the same controller? Additional testing
and/or input from the hardware vendor would be needed. If you have multiple
controllers/storage systems, then spreading multiple files across the
controllers should give a benefit. Whether you need to do this would depend
on your app.
HTH,
Mike Kruchten
"Peter Daniels" <nospampedro@.yahoo.com> wrote in message
news:2fd8f155.0403291246.190893b6@.posting.google.com...
> I've looked at many of the threads in this group that discuss whether
> or not multiple data files in a filegroup will improve IO performance.
> Some say that the BOL is wrong and that you don't need additional
> files to have SQL server use parallel IO (starting with SQL 2K).
> Others say that it still helps even in SQL 2K. I'm planning on doing
> some benchmarking, but thought I'd throw it out there one more time.
> What is Microsoft's stance on this? Should we or should we not use
> multiple files in a filegroup. If we should, do we use the rule of
> thumb of roughly 1 file per spindle in the RAID?
> Thanks!
> -Peter|||Sounds like the answer is no - mutiple data files in a single file
group on one RAID array will not give an IO perf benefit. I'm still
going to do some benchmarking, but thanks very much for your input.
I'd still love to here MS's official statement on this.
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message news:<OePgmzmFEHA.2768@.tk2msf
tngp13.phx.gbl>...
> The last answer I got, from someone working for Microsoft Consulting
> Services, was that there was no benefit to multiple files on the same disk
> volume. However if you have multiple volumes you MAY see improvements.
> It depends on the IO characteristics of your disk volumes. Example, if you
> had 20 spindles would it be better to create one large RAID volume or >1
> smaller volumes if they're all on the same controller? Additional testing
> and/or input from the hardware vendor would be needed. If you have multipl
e
> controllers/storage systems, then spreading multiple files across the
> controllers should give a benefit. Whether you need to do this would depen
d
> on your app.
> HTH,
> Mike Kruchten
>
> "Peter Daniels" <nospampedro@.yahoo.com> wrote in message
> news:2fd8f155.0403291246.190893b6@.posting.google.com...|||This has been debated extensively in the private MVP group, with MS develope
rs as participants in the
discussion. I don't think you will find an official answer from MS, and if y
ou do, it will probably be very
close to what Mark posted.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter Daniels" <nospampedro@.yahoo.com> wrote in message
news:2fd8f155.0403301510.5e0602a5@.posting.google.com...
> Sounds like the answer is no - mutiple data files in a single file
> group on one RAID array will not give an IO perf benefit. I'm still
> going to do some benchmarking, but thanks very much for your input.
> I'd still love to here MS's official statement on this.
>