multiple files for 1 group but same "disk"... performance?

Hi,
To try to improve my performance on a system where I have no control (a long
long story...)
My problem:
the HD subsystem is "bad", I'm on a SAN and most of my wait types are caused
by the disks.
I have only 1 disk available (300gb in Raid 5, but I don't know how many
disks behind this)
so I have 1 partition for my database!!! (the throughput is bad)
My server has 4 processors (8 with HT enabled)
I know that SQL perform better if I have 1 file by processor because SQL
server create 1 thread by file (I'm right?)
so, if I create 8 files but all in the same group, does this help me?
if yes, how can I redistribute the usage of my current tables, which are in
only 1 file for the moment, on all files (without having to copy the tables
in a new table)
or I have to create 8 groups with 1 file only and choose which table I put
in which group?
or this idea will improove... nothing!
thanks.
jeromeHello Jeje,
Seems we are in the same boat, I too have a large SAN with RAID 5 and
500 gb over 10 disks.
The optimization I have gone with is creating file groups for large
tables and sperate file groups for indexes.
Eg
FactSalesData -fact table data
FactSalesIndex - fact table index
You can only specify a file group for a table to use; you can't
specify the file as far as I know.
One other thing to consider is the use of logical drives on you single
partition, this will allow you to stream the disk queues via the OS,
well that's the theory.
So with sperate file groups for indexes and data you should reduce the
file lock contention.
For more tips on file groups see
http://www.sql-server-performance.com/filegroups.asp
Also check out http://www.sql-server-performance.com/ew_san.asp
It has some great ideas on SAN configuration.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/|||thanks
do you see any improvement?
my firsts tests wher I divide my primary group into 8 files don't change
anything (or a too small change)
I'll try to use separated filegroups.
<Myles.Matheson@.gmail.com> wrote in message
news:1124192466.494874.179750@.f14g2000cwb.googlegroups.com...
> Hello Jeje,
> Seems we are in the same boat, I too have a large SAN with RAID 5 and
> 500 gb over 10 disks.
> The optimization I have gone with is creating file groups for large
> tables and sperate file groups for indexes.
> Eg
> FactSalesData -fact table data
> FactSalesIndex - fact table index
> You can only specify a file group for a table to use; you can't
> specify the file as far as I know.
> One other thing to consider is the use of logical drives on you single
> partition, this will allow you to stream the disk queues via the OS,
> well that's the theory.
> So with sperate file groups for indexes and data you should reduce the
> file lock contention.
> For more tips on file groups see
> http://www.sql-server-performance.com/filegroups.asp
>
> Also check out http://www.sql-server-performance.com/ew_san.asp
> It has some great ideas on SAN configuration.
> Hope this helps,
>
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>

multiple files for 1 group but same "disk"... performance?

Hi,
To try to improve my performance on a system where I have no control (a long
long story...)
My problem:
the HD subsystem is "bad", I'm on a SAN and most of my wait types are caused
by the disks.
I have only 1 disk available (300gb in Raid 5, but I don't know how many
disks behind this)
so I have 1 partition for my database!!! (the throughput is bad)
My server has 4 processors (8 with HT enabled)
I know that SQL perform better if I have 1 file by processor because SQL
server create 1 thread by file (I'm right?)
so, if I create 8 files but all in the same group, does this help me?
if yes, how can I redistribute the usage of my current tables, which are in
only 1 file for the moment, on all files (without having to copy the tables
in a new table)
or I have to create 8 groups with 1 file only and choose which table I put
in which group?
or this idea will improove... nothing!
thanks.
jerome
Hello Jeje,
Seems we are in the same boat, I too have a large SAN with RAID 5 and
500 gb over 10 disks.
The optimization I have gone with is creating file groups for large
tables and sperate file groups for indexes.
Eg
FactSalesData -fact table data
FactSalesIndex - fact table index
You can only specify a file group for a table to use; you can't
specify the file as far as I know.
One other thing to consider is the use of logical drives on you single
partition, this will allow you to stream the disk queues via the OS,
well that's the theory.
So with sperate file groups for indexes and data you should reduce the
file lock contention.
For more tips on file groups see
http://www.sql-server-performance.com/filegroups.asp
Also check out http://www.sql-server-performance.com/ew_san.asp
It has some great ideas on SAN configuration.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
|||thanks
do you see any improvement?
my firsts tests wher I divide my primary group into 8 files don't change
anything (or a too small change)
I'll try to use separated filegroups.
<Myles.Matheson@.gmail.com> wrote in message
news:1124192466.494874.179750@.f14g2000cwb.googlegr oups.com...
> Hello Jeje,
> Seems we are in the same boat, I too have a large SAN with RAID 5 and
> 500 gb over 10 disks.
> The optimization I have gone with is creating file groups for large
> tables and sperate file groups for indexes.
> Eg
> FactSalesData -fact table data
> FactSalesIndex - fact table index
> You can only specify a file group for a table to use; you can't
> specify the file as far as I know.
> One other thing to consider is the use of logical drives on you single
> partition, this will allow you to stream the disk queues via the OS,
> well that's the theory.
> So with sperate file groups for indexes and data you should reduce the
> file lock contention.
> For more tips on file groups see
> http://www.sql-server-performance.com/filegroups.asp
>
> Also check out http://www.sql-server-performance.com/ew_san.asp
> It has some great ideas on SAN configuration.
> Hope this helps,
>
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>
sql

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