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/
>
Showing posts with label subsystem. Show all posts
Showing posts with label subsystem. Show all posts
Friday, March 23, 2012
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
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
Friday, March 9, 2012
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.
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.
Subscribe to:
Posts (Atom)