Format Alignment: belong

Is there a way to see which table belong to which file of a file group? I
have a filegroup of 10 files and they files are not evenly distributed. One
hold 16G while the other in 3G range. Thanks!Tables only can be assigned to a specific filegroup. When you right click
the table name in Enterprise Manager and script it, it will tell you what
file group it is on
eg
CREATE TABLE ....
( ...
)
ON PRIMARY
The above example is the primary group.
What SQL does is when you add objects to a filegroup is fill up the first
file, then move to the next, move to the next after that second one was
filled and so on. That is why you see the uneven file distribution. You'll
have to dig around in SQL books online, but it should be in there somewhere.
I learned this studying to recertify on SQL 2000.
--
***********************************
Andy S.
andy_mcdba@.yahoo.com
***********************************
"Kevin" <kevin@.noemail.com> wrote in message
news:%23YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> Is there a way to see which table belong to which file of a file group? I
> have a filegroup of 10 files and they files are not evenly distributed.
One
> hold 16G while the other in 3G range. Thanks!
>|||This is a multi-part message in MIME format.
--=_NextPart_000_003A_01C37DE2.477867E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Andy,
I'm not sure this is true. Everthing should be distributed equally =across the data files stored on diferrent disk drives.
BOL:
"Filegroups use a proportional fill strategy across all the files within =each filegroup. As data is written to the filegroup, Microsoft=AE SQL =ServerT 2000 writes an amount proportional to the free space in the file =to each file within the filegroup, rather than writing all the data to =the first file until full, and then writing to the next file. For =example, if file f1 has 100 megabytes (MB) free and file f2 has 200 MB =free, one extent is allocated from file f1, two extents from file f2, =and so on. This way both files become full at about the same time, and =simple striping is achieved."
Thanks,
Eddie
"Andy Svendsen" <andy_mcdba@.yahoo.com> wrote in message =news:%23mvLcGgfDHA.2236@.TK2MSFTNGP12.phx.gbl...
> Tables only can be assigned to a specific filegroup. When you right =click
> the table name in Enterprise Manager and script it, it will tell you =what
> file group it is on
> > eg
> > CREATE TABLE ....
> ( ...
> )
> ON PRIMARY
> > The above example is the primary group.
> > What SQL does is when you add objects to a filegroup is fill up the =first
> file, then move to the next, move to the next after that second one =was
> filled and so on. That is why you see the uneven file distribution. =You'll
> have to dig around in SQL books online, but it should be in there =somewhere.
> I learned this studying to recertify on SQL 2000.
> > -- > ***********************************
> Andy S.
> andy_mcdba@.yahoo.com
> > ***********************************
> "Kevin" <kevin@.noemail.com> wrote in message
> news:%23YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> > Is there a way to see which table belong to which file of a file =group? I
> > have a filegroup of 10 files and they files are not evenly =distributed.
> One
> > hold 16G while the other in 3G range. Thanks!
> >
> >
> >
--=_NextPart_000_003A_01C37DE2.477867E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Andy,
I'm not sure this is true. Everthing =should be distributed equally across the data files stored on diferrent disk drives.
BOL:
"Filegroups use a proportional fill =strategy across all the files within each filegroup. As data is written to the =filegroup, Microsoft=AE SQL Server=99 2000 writes an amount proportional to the =free space in the file to each file within the filegroup, rather than writing all the =data to the first file until full, and then writing to the next file. For =example, if file f1 has 100 megabytes (MB) free and file f2 has 200 MB free, one =extent is allocated from file f1, two extents from file f2, and so on. This way =both files become full at about the same time, and simple striping is achieved."
Thanks,
Eddie
"Andy Svendsen" =wrote in message news:%23mvLcGgfDHA.2236@.TK2MSFTNGP12.phx.gbl...> =Tables only can be assigned to a specific filegroup. When you right click> =the table name in Enterprise Manager and script it, it will tell you =what> file group it is on> > eg> > CREATE TABLE ....> ( ...> )> ON PRIMARY> > The =above example is the primary group.> > What SQL does is when you =add objects to a filegroup is fill up the first> file, then move to =the next, move to the next after that second one was> filled and so =on. That is why you see the uneven file distribution. You'll> have =to dig around in SQL books online, but it should be in there somewhere.> =I learned this studying to recertify on SQL 2000.> > -- => ***********************************> Andy S.> andy_mcdba@.yahoo.com> > ***********************************> "Kevin" =wrote in message> news:%23YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...> > Is there a way to see which table belong to =which file of a file group? I> > have a filegroup of 10 files and =they files are not evenly distributed.> One> > hold 16G =while the other in 3G range. Thanks!> >> >> =>

--=_NextPart_000_003A_01C37DE2.477867E0--|||I am not aware of any way you can easily discover WHICH file in a filegroup
contains the datapages for a table... You COULD look at the IAM page in each
file for the table and do some stuff, but it would be manual...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kevin" <kevin@.noemail.com> wrote in message
news:#YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> Is there a way to see which table belong to which file of a file group? I
> have a filegroup of 10 files and they files are not evenly distributed.
One
> hold 16G while the other in 3G range. Thanks!
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0047_01C37DE7.0F78ACB0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
And if it is so, how come that I get significantly higher usage on one =of my disks (10 times), if I have only one filegroup, which is spread =across 7 hard disks (each of them has one or two data files)? It seems =to me that practically, database engine uses diferrent strategy...
Eddie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eammxQgfDHA.128@.tk2msftngp13.phx.gbl...
Not quite. It uses a proportional fill strategy. From the BOL:
"Filegroups use a proportional fill strategy across all the files =within each filegroup. As data is written to the filegroup, Microsoft=AE =SQL Server=99 2000 writes an amount proportional to the free space in =the file to each file within the filegroup, rather than writing all the =data to the first file until full, and then writing to the next file. =For example, if file f1 has 100 megabytes (MB) free and file f2 has 200 =MB free, one extent is allocated from file f1, two extents from file f2, =and so on. This way both files become full at about the same time, and =simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server =automatically expands one file at a time in a round-robin fashion to =accommodate more data (provided that the database is set to grow =automatically). For example, a filegroup comprises three files, all set =to automatically grow. When space in all files in the filegroup is =exhausted, only the first file is expanded. When the first file is full, =and no more data can be written to the filegroup, the second file is =expanded. When the second file is full, and no more data can be written =to the filegroup, the third file is expanded. If the third file becomes =full, and no more data can be written to the filegroup, the first file =is expanded again, and so on."
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andy Svendsen" <andy_mcdba@.yahoo.com> wrote in message =news:#mvLcGgfDHA.2236@.TK2MSFTNGP12.phx.gbl...
Tables only can be assigned to a specific filegroup. When you right =click
the table name in Enterprise Manager and script it, it will tell you =what
file group it is on
eg
CREATE TABLE ....
( ...
)
ON PRIMARY
The above example is the primary group.
What SQL does is when you add objects to a filegroup is fill up the =first
file, then move to the next, move to the next after that second one =was
filled and so on. That is why you see the uneven file distribution. =You'll
have to dig around in SQL books online, but it should be in there =somewhere.
I learned this studying to recertify on SQL 2000.
-- ***********************************
Andy S.
andy_mcdba@.yahoo.com
***********************************
"Kevin" <kevin@.noemail.com> wrote in message
news:%23YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> Is there a way to see which table belong to which file of a file =group? I
> have a filegroup of 10 files and they files are not evenly =distributed.
One
> hold 16G while the other in 3G range. Thanks!
>
>
--=_NextPart_000_0047_01C37DE7.0F78ACB0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

And if it is so, how come that I get =significantly higher usage on one of my disks (10 times), if I have only one =filegroup, which is spread across 7 hard disks (each of them has one or two data files)? =It seems to me that practically, database engine uses diferrent =strategy...
Eddie
"Tom Moreau" = wrote in message news:eammxQgfDHA.128@.t=k2msftngp13.phx.gbl...
Not quite. It uses a =proportional fill strategy. From the BOL:

"Filegroups use a proportional fill strategy across all =the files within each filegroup. As data is written to the filegroup, =Microsoft=AE SQL Server=99 2000 writes an amount proportional to the free space in the =file to each file within the filegroup, rather than writing all the data to =the first file until full, and then writing to the next file. For example, if =file f1 has 100 megabytes (MB) free and file f2 has 200 MB =free, one extent is allocated from file f1, two extents from file =f2, and so on. This way both files become full at about the same time, and =simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server =automatically expands one file at a time in a round-robin fashion to accommodate =more data (provided that the database is set to grow automatically). For =example, a filegroup comprises three files, all set to automatically grow. When =space in all files in the filegroup is exhausted, only the first file is =expanded. When the first file is full, and no more data can be written to the =filegroup, the second file is expanded. When the second file is full, and no more =data can be written to the filegroup, the third file is expanded. If the third =file becomes full, and no more data can be written to the filegroup, the =first file is expanded again, and so on."
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Andy Svendsen" =wrote in message news:#mvLcGgfDHA.2236=@.TK2MSFTNGP12.phx.gbl...Tables only can be assigned to a specific filegroup. When you right clickthe table name in Enterprise Manager and script it, it will =tell you whatfile group it is onegCREATE TABLE ....( ...)ON PRIMARYThe above example is the primary group.What SQL does is when you add objects to a filegroup is =fill up the firstfile, then move to the next, move to the next after that =second one wasfilled and so on. That is why you see the uneven file = distribution. You'llhave to dig around in SQL books online, =but it should be in there somewhere.I learned this studying to recertify =on SQL 2000.-- ***********************************Andy =S.andy_mcdba@.yahoo.com****=*******************************"Kevin" =wrote in messagenews:%23YdaQAgfDHA.5=76@.tk2msftngp13.phx.gbl...> Is there a way to see which table belong to which file of a file =group? I> have a filegroup of 10 files and they files are not evenly distributed.One> hold 16G while the other in 3G =range. Thanks!>>

--=_NextPart_000_0047_01C37DE7.0F78ACB0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0316_01C37DE9.2183E5D0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
In the 2nd paragraph of the BOL extract below, it mentions what happens =with file growth. Basically, as long as you stay ahead of the file =growth yourself, it will do proportional fill. However, once everything =fills, you start expanding and filling in a round-robin fashion.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eddie" <espasojevic@.rogers.com> wrote in message =news:#EoHZigfDHA.3528@.tk2msftngp13.phx.gbl...
And if it is so, how come that I get significantly higher usage on one =of my disks (10 times), if I have only one filegroup, which is spread =across 7 hard disks (each of them has one or two data files)? It seems =to me that practically, database engine uses diferrent strategy...
Eddie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eammxQgfDHA.128@.tk2msftngp13.phx.gbl...
Not quite. It uses a proportional fill strategy. From the BOL:
"Filegroups use a proportional fill strategy across all the files =within each filegroup. As data is written to the filegroup, Microsoft=AE =SQL Server=99 2000 writes an amount proportional to the free space in =the file to each file within the filegroup, rather than writing all the =data to the first file until full, and then writing to the next file. =For example, if file f1 has 100 megabytes (MB) free and file f2 has 200 =MB free, one extent is allocated from file f1, two extents from file f2, =and so on. This way both files become full at about the same time, and =simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server =automatically expands one file at a time in a round-robin fashion to =accommodate more data (provided that the database is set to grow =automatically). For example, a filegroup comprises three files, all set =to automatically grow. When space in all files in the filegroup is =exhausted, only the first file is expanded. When the first file is full, =and no more data can be written to the filegroup, the second file is =expanded. When the second file is full, and no more data can be written =to the filegroup, the third file is expanded. If the third file becomes =full, and no more data can be written to the filegroup, the first file =is expanded again, and so on."
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andy Svendsen" <andy_mcdba@.yahoo.com> wrote in message =news:#mvLcGgfDHA.2236@.TK2MSFTNGP12.phx.gbl...
Tables only can be assigned to a specific filegroup. When you right =click
the table name in Enterprise Manager and script it, it will tell you =what
file group it is on
eg
CREATE TABLE ....
( ...
)
ON PRIMARY
The above example is the primary group.
What SQL does is when you add objects to a filegroup is fill up the =first
file, then move to the next, move to the next after that second one =was
filled and so on. That is why you see the uneven file distribution. =You'll
have to dig around in SQL books online, but it should be in there =somewhere.
I learned this studying to recertify on SQL 2000.
-- ***********************************
Andy S.
andy_mcdba@.yahoo.com
***********************************
"Kevin" <kevin@.noemail.com> wrote in message
news:%23YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> Is there a way to see which table belong to which file of a file =group? I
> have a filegroup of 10 files and they files are not evenly =distributed.
One
> hold 16G while the other in 3G range. Thanks!
>
>
--=_NextPart_000_0316_01C37DE9.2183E5D0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

In the 2nd paragraph of the BOL =extract below, it mentions what happens with file growth. Basically, as long as you =stay ahead of the file growth yourself, it will do proportional fill. =However, once everything fills, you start expanding and filling in a round-robin fashion.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Eddie" =wrote in message news:#EoHZigfDHA.3528=@.tk2msftngp13.phx.gbl...
And if it is so, how come that I get =significantly higher usage on one of my disks (10 times), if I have only one =filegroup, which is spread across 7 hard disks (each of them has one or two data files)? =It seems to me that practically, database engine uses diferrent =strategy...
Eddie
"Tom Moreau" = wrote in message news:eammxQgfDHA.128@.t=k2msftngp13.phx.gbl...
Not quite. It uses a =proportional fill strategy. From the BOL:

"Filegroups use a proportional fill strategy across all =the files within each filegroup. As data is written to the filegroup, =Microsoft=AE SQL Server=99 2000 writes an amount proportional to the free space in the =file to each file within the filegroup, rather than writing all the data to =the first file until full, and then writing to the next file. For example, if =file f1 has 100 megabytes (MB) free and file f2 has 200 MB =free, one extent is allocated from file f1, two extents from file =f2, and so on. This way both files become full at about the same time, and =simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server =automatically expands one file at a time in a round-robin fashion to accommodate =more data (provided that the database is set to grow automatically). For =example, a filegroup comprises three files, all set to automatically grow. When =space in all files in the filegroup is exhausted, only the first file is =expanded. When the first file is full, and no more data can be written to the =filegroup, the second file is expanded. When the second file is full, and no more =data can be written to the filegroup, the third file is expanded. If the third =file becomes full, and no more data can be written to the filegroup, the =first file is expanded again, and so on."
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Andy Svendsen" =wrote in message news:#mvLcGgfDHA.2236=@.TK2MSFTNGP12.phx.gbl...Tables only can be assigned to a specific filegroup. When you right clickthe table name in Enterprise Manager and script it, it will =tell you whatfile group it is onegCREATE TABLE ....( ...)ON PRIMARYThe above example is the primary group.What SQL does is when you add objects to a filegroup is =fill up the firstfile, then move to the next, move to the next after that =second one wasfilled and so on. That is why you see the uneven file = distribution. You'llhave to dig around in SQL books online, =but it should be in there somewhere.I learned this studying to recertify =on SQL 2000.-- ***********************************Andy =S.andy_mcdba@.yahoo.com****=*******************************"Kevin" =wrote in messagenews:%23YdaQAgfDHA.5=76@.tk2msftngp13.phx.gbl...> Is there a way to see which table belong to which file of a file =group? I> have a filegroup of 10 files and they files are not evenly distributed.One> hold 16G while the other in 3G =range. Thanks!>>

--=_NextPart_000_0316_01C37DE9.2183E5D0--|||What the book said and reality do not go together. I read BOL and couple
books and they said the way to do horizontal partition of a database is to
create filegroups accross multiple physical disks.
I have the database of 120G and it was there since SQL 6.5 & 7.0 (currently
in SQL2K) and it is growing; therefore, the filegroups are unevenly
distributed. I create a new database from scratch with new filegroups to
gain better performance. Once it is done, I load the data from source
database to new database sequentially. Technically (based on books & BOL),
the data should be load in round-robin method, but they didn't. Couple
tables are in the 20G (data + indexes) ranges and they need horizontal
partition. If I can figure out which tables cause the unevenly
distribution, I can reload them again.
Looking IAM could help but it could take a while to get the numbers.
If there is a better to do horizontal partition, please show me how to do
it. Because BOL & couple books I got are too general in partitioning.
Thanks!
Kevin
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:e9tbCRgfDHA.1200@.TK2MSFTNGP09.phx.gbl...
> I am not aware of any way you can easily discover WHICH file in a
filegroup
> contains the datapages for a table... You COULD look at the IAM page in
each
> file for the table and do some stuff, but it would be manual...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:#YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> > Is there a way to see which table belong to which file of a file group?
I
> > have a filegroup of 10 files and they files are not evenly distributed.
> One
> > hold 16G while the other in 3G range. Thanks!
> >
> >
>|||The way you get out of balance is...
1. you add new files to the filegroup when tables with records already
exist.. SQL does NOT automatically rebalance.
2. Even tho , as others have quoted, SQL will use proportional fill to place
equivalent amounts of data across all files... users can still delete data
from a table, leaving the remaining records mostly on one filegroup...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kevin" <kevin@.noemail.com> wrote in message
news:#YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> Is there a way to see which table belong to which file of a file group? I
> have a filegroup of 10 files and they files are not evenly distributed.
One
> hold 16G while the other in 3G range. Thanks!
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0018_01C37DF8.031CB400
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
We don't have automatic growth option turned on on any of the data =files. We monitor and expand the files manually, according to the free =space in the database. So far, we had several data file expansions, but =only when database population was around 80%. Neather data file was =completely populated, so according to BOL, as I understand, database =engine shouldn't have started using round-robin... Eddie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23FUTuqgfDHA.944@.TK2MSFTNGP11.phx.gbl...
In the 2nd paragraph of the BOL extract below, it mentions what =happens with file growth. Basically, as long as you stay ahead of the =file growth yourself, it will do proportional fill. However, once =everything fills, you start expanding and filling in a round-robin =fashion.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eddie" <espasojevic@.rogers.com> wrote in message =news:#EoHZigfDHA.3528@.tk2msftngp13.phx.gbl...
And if it is so, how come that I get significantly higher usage on one =of my disks (10 times), if I have only one filegroup, which is spread =across 7 hard disks (each of them has one or two data files)? It seems =to me that practically, database engine uses diferrent strategy...
Eddie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eammxQgfDHA.128@.tk2msftngp13.phx.gbl...
Not quite. It uses a proportional fill strategy. From the BOL:
"Filegroups use a proportional fill strategy across all the files =within each filegroup. As data is written to the filegroup, Microsoft=AE =SQL Server=99 2000 writes an amount proportional to the free space in =the file to each file within the filegroup, rather than writing all the =data to the first file until full, and then writing to the next file. =For example, if file f1 has 100 megabytes (MB) free and file f2 has 200 =MB free, one extent is allocated from file f1, two extents from file f2, =and so on. This way both files become full at about the same time, and =simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server =automatically expands one file at a time in a round-robin fashion to =accommodate more data (provided that the database is set to grow =automatically). For example, a filegroup comprises three files, all set =to automatically grow. When space in all files in the filegroup is =exhausted, only the first file is expanded. When the first file is full, =and no more data can be written to the filegroup, the second file is =expanded. When the second file is full, and no more data can be written =to the filegroup, the third file is expanded. If the third file becomes =full, and no more data can be written to the filegroup, the first file =is expanded again, and so on."
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andy Svendsen" <andy_mcdba@.yahoo.com> wrote in message =news:#mvLcGgfDHA.2236@.TK2MSFTNGP12.phx.gbl...
Tables only can be assigned to a specific filegroup. When you right =click
the table name in Enterprise Manager and script it, it will tell you =what
file group it is on
eg
CREATE TABLE ....
( ...
)
ON PRIMARY
The above example is the primary group.
What SQL does is when you add objects to a filegroup is fill up the =first
file, then move to the next, move to the next after that second one =was
filled and so on. That is why you see the uneven file distribution. = You'll
have to dig around in SQL books online, but it should be in there =somewhere.
I learned this studying to recertify on SQL 2000.
-- ***********************************
Andy S.
andy_mcdba@.yahoo.com
***********************************
"Kevin" <kevin@.noemail.com> wrote in message
news:%23YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> Is there a way to see which table belong to which file of a file =group? I
> have a filegroup of 10 files and they files are not evenly =distributed.
One
> hold 16G while the other in 3G range. Thanks!
>
>
--=_NextPart_000_0018_01C37DF8.031CB400
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

We don't have automatic growth option =turned on on any of the data files. We monitor and expand the files manually, =according to the free space in the database. So far, we had several data file expansions, but only when database population was around =80%. Neather data file was completely populated, so according to BOL, as I =understand, database engine shouldn't have started using round-robin... =Eddie
"Tom Moreau" = wrote in message news:%23FUTuqgfDHA.9=44@.TK2MSFTNGP11.phx.gbl...
In the 2nd paragraph of the BOL =extract below, it mentions what happens with file growth. Basically, as long as =you stay ahead of the file growth yourself, it will do proportional =fill. However, once everything fills, you start expanding and filling in a round-robin fashion.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Eddie" =wrote in message news:#EoHZigfDHA.3528=@.tk2msftngp13.phx.gbl...
And if it is so, how come that I get significantly higher usage on one of my disks (10 times), if I have =only one filegroup, which is spread across 7 hard disks (each of them has one =or two data files)? It seems to me that practically, database engine uses =diferrent strategy...
Eddie
"Tom Moreau" = wrote in message news:eammxQgfDHA.128@.t=k2msftngp13.phx.gbl...
Not quite. It uses a =proportional fill strategy. From the BOL:

"Filegroups use a proportional fill strategy across =all the files within each filegroup. As data is written to the filegroup, =Microsoft=AE SQL Server=99 2000 writes an amount proportional to the free space =in the file to each file within the filegroup, rather than writing all the data =to the first file until full, and then writing to the next file. For =example, if file f1 has 100 megabytes (MB) free and file f2 has =200 MB free, one extent is allocated from file f1, two extents from =file f2, and so on. This way both files become full at about the =same time, and simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server automatically expands one file at a time in a round-robin fashion to = accommodate more data (provided that the database is set to grow automatically). For example, a filegroup comprises three files, all =set to automatically grow. When space in all files in the filegroup is =exhausted, only the first file is expanded. When the first file is full, and no =more data can be written to the filegroup, the second file is expanded. =When the second file is full, and no more data can be written to the =filegroup, the third file is expanded. If the third file becomes full, and no more =data can be written to the filegroup, the first file is expanded again, and =so on."
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Andy Svendsen" =wrote in message news:#mvLcGgfDHA.2236=@.TK2MSFTNGP12.phx.gbl...Tables only can be assigned to a specific filegroup. When you right clickthe table name in Enterprise Manager and script it, it will =tell you whatfile group it is onegCREATE TABLE =....( ...)ON PRIMARYThe above example is the primary group.What SQL does is when you add objects to a filegroup =is fill up the firstfile, then move to the next, move to the next after =that second one wasfilled and so on. That is why you see the =uneven file distribution. You'llhave to dig around in SQL books =online, but it should be in there somewhere.I learned this studying to =recertify on SQL 2000.-- =***********************************Andy S.andy_mcdba@.yahoo.com****=*******************************"Kevin" =wrote in messagenews:%23YdaQAgfDHA.5=76@.tk2msftngp13.phx.gbl...> Is there a way to see which table belong to which file of a file group? I> have a filegroup of 10 files and they files =are not evenly distributed.One> hold 16G while the other in 3G range. Thanks!>>

--=_NextPart_000_0018_01C37DF8.031CB400--|||This is a multi-part message in MIME format.
--=_NextPart_000_047F_01C37DFB.D0ED9400
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I'll see what I can get from MS.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eddie" <espasojevic@.rogers.com> wrote in message =news:ekW7MmhfDHA.3464@.TK2MSFTNGP11.phx.gbl...
We don't have automatic growth option turned on on any of the data =files. We monitor and expand the files manually, according to the free =space in the database. So far, we had several data file expansions, but =only when database population was around 80%. Neather data file was =completely populated, so according to BOL, as I understand, database =engine shouldn't have started using round-robin... Eddie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23FUTuqgfDHA.944@.TK2MSFTNGP11.phx.gbl...
In the 2nd paragraph of the BOL extract below, it mentions what =happens with file growth. Basically, as long as you stay ahead of the =file growth yourself, it will do proportional fill. However, once =everything fills, you start expanding and filling in a round-robin =fashion.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eddie" <espasojevic@.rogers.com> wrote in message =news:#EoHZigfDHA.3528@.tk2msftngp13.phx.gbl...
And if it is so, how come that I get significantly higher usage on one =of my disks (10 times), if I have only one filegroup, which is spread =across 7 hard disks (each of them has one or two data files)? It seems =to me that practically, database engine uses diferrent strategy...
Eddie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eammxQgfDHA.128@.tk2msftngp13.phx.gbl...
Not quite. It uses a proportional fill strategy. From the BOL:
"Filegroups use a proportional fill strategy across all the files =within each filegroup. As data is written to the filegroup, Microsoft=AE =SQL Server=99 2000 writes an amount proportional to the free space in =the file to each file within the filegroup, rather than writing all the =data to the first file until full, and then writing to the next file. =For example, if file f1 has 100 megabytes (MB) free and file f2 has 200 =MB free, one extent is allocated from file f1, two extents from file f2, =and so on. This way both files become full at about the same time, and =simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server =automatically expands one file at a time in a round-robin fashion to =accommodate more data (provided that the database is set to grow =automatically). For example, a filegroup comprises three files, all set =to automatically grow. When space in all files in the filegroup is =exhausted, only the first file is expanded. When the first file is full, =and no more data can be written to the filegroup, the second file is =expanded. When the second file is full, and no more data can be written =to the filegroup, the third file is expanded. If the third file becomes =full, and no more data can be written to the filegroup, the first file =is expanded again, and so on."
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andy Svendsen" <andy_mcdba@.yahoo.com> wrote in message =news:#mvLcGgfDHA.2236@.TK2MSFTNGP12.phx.gbl...
Tables only can be assigned to a specific filegroup. When you right =click
the table name in Enterprise Manager and script it, it will tell you =what
file group it is on
eg
CREATE TABLE ....
( ...
)
ON PRIMARY
The above example is the primary group.
What SQL does is when you add objects to a filegroup is fill up the =first
file, then move to the next, move to the next after that second one =was
filled and so on. That is why you see the uneven file distribution. = You'll
have to dig around in SQL books online, but it should be in there =somewhere.
I learned this studying to recertify on SQL 2000.
-- ***********************************
Andy S.
andy_mcdba@.yahoo.com
***********************************
"Kevin" <kevin@.noemail.com> wrote in message
news:%23YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> Is there a way to see which table belong to which file of a file =group? I
> have a filegroup of 10 files and they files are not evenly =distributed.
One
> hold 16G while the other in 3G range. Thanks!
>
>
--=_NextPart_000_047F_01C37DFB.D0ED9400
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

I'll see what I can get from =MS.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Eddie" =wrote in message news:ekW7MmhfDHA.3464=@.TK2MSFTNGP11.phx.gbl...
We don't have automatic growth option =turned on on any of the data files. We monitor and expand the files manually, =according to the free space in the database. So far, we had several data file expansions, but only when database population was around =80%. Neather data file was completely populated, so according to BOL, as I =understand, database engine shouldn't have started using round-robin... =Eddie
"Tom Moreau" = wrote in message news:%23FUTuqgfDHA.9=44@.TK2MSFTNGP11.phx.gbl...
In the 2nd paragraph of the BOL =extract below, it mentions what happens with file growth. Basically, as long as =you stay ahead of the file growth yourself, it will do proportional =fill. However, once everything fills, you start expanding and filling in a round-robin fashion.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Eddie" =wrote in message news:#EoHZigfDHA.3528=@.tk2msftngp13.phx.gbl...
And if it is so, how come that I get significantly higher usage on one of my disks (10 times), if I have =only one filegroup, which is spread across 7 hard disks (each of them has one =or two data files)? It seems to me that practically, database engine uses =diferrent strategy...
Eddie
"Tom Moreau" = wrote in message news:eammxQgfDHA.128@.t=k2msftngp13.phx.gbl...
Not quite. It uses a =proportional fill strategy. From the BOL:

"Filegroups use a proportional fill strategy across =all the files within each filegroup. As data is written to the filegroup, =Microsoft=AE SQL Server=99 2000 writes an amount proportional to the free space =in the file to each file within the filegroup, rather than writing all the data =to the first file until full, and then writing to the next file. For =example, if file f1 has 100 megabytes (MB) free and file f2 has =200 MB free, one extent is allocated from file f1, two extents from =file f2, and so on. This way both files become full at about the =same time, and simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server automatically expands one file at a time in a round-robin fashion to = accommodate more data (provided that the database is set to grow automatically). For example, a filegroup comprises three files, all =set to automatically grow. When space in all files in the filegroup is =exhausted, only the first file is expanded. When the first file is full, and no =more data can be written to the filegroup, the second file is expanded. =When the second file is full, and no more data can be written to the =filegroup, the third file is expanded. If the third file becomes full, and no more =data can be written to the filegroup, the first file is expanded again, and =so on."
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Andy Svendsen" =wrote in message news:#mvLcGgfDHA.2236=@.TK2MSFTNGP12.phx.gbl...Tables only can be assigned to a specific filegroup. When you right clickthe table name in Enterprise Manager and script it, it will =tell you whatfile group it is onegCREATE TABLE =....( ...)ON PRIMARYThe above example is the primary group.What SQL does is when you add objects to a filegroup =is fill up the firstfile, then move to the next, move to the next after =that second one wasfilled and so on. That is why you see the =uneven file distribution. You'llhave to dig around in SQL books =online, but it should be in there somewhere.I learned this studying to =recertify on SQL 2000.-- =***********************************Andy S.andy_mcdba@.yahoo.com****=*******************************"Kevin" =wrote in messagenews:%23YdaQAgfDHA.5=76@.tk2msftngp13.phx.gbl...> Is there a way to see which table belong to which file of a file group? I> have a filegroup of 10 files and they files =are not evenly distributed.One> hold 16G while the other in 3G range. Thanks!>>

--=_NextPart_000_047F_01C37DFB.D0ED9400--|||This is a multi-part message in MIME format.
--=_NextPart_000_002C_01C37E13.7C7581E0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Could you please post the version of SQL Server you are using?
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Eddie" <espasojevic@.rogers.com> wrote in message =news:ekW7MmhfDHA.3464@.TK2MSFTNGP11.phx.gbl...
We don't have automatic growth option turned on on any of the data =files. We monitor and expand the files manually, according to the free =space in the database. So far, we had several data file expansions, but =only when database population was around 80%. Neather data file was =completely populated, so according to BOL, as I understand, database =engine shouldn't have started using round-robin... Eddie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23FUTuqgfDHA.944@.TK2MSFTNGP11.phx.gbl...
In the 2nd paragraph of the BOL extract below, it mentions what =happens with file growth. Basically, as long as you stay ahead of the =file growth yourself, it will do proportional fill. However, once =everything fills, you start expanding and filling in a round-robin =fashion.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eddie" <espasojevic@.rogers.com> wrote in message =news:#EoHZigfDHA.3528@.tk2msftngp13.phx.gbl...
And if it is so, how come that I get significantly higher usage on one =of my disks (10 times), if I have only one filegroup, which is spread =across 7 hard disks (each of them has one or two data files)? It seems =to me that practically, database engine uses diferrent strategy...
Eddie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eammxQgfDHA.128@.tk2msftngp13.phx.gbl...
Not quite. It uses a proportional fill strategy. From the BOL:
"Filegroups use a proportional fill strategy across all the files =within each filegroup. As data is written to the filegroup, Microsoft=AE =SQL Server=99 2000 writes an amount proportional to the free space in =the file to each file within the filegroup, rather than writing all the =data to the first file until full, and then writing to the next file. =For example, if file f1 has 100 megabytes (MB) free and file f2 has 200 =MB free, one extent is allocated from file f1, two extents from file f2, =and so on. This way both files become full at about the same time, and =simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server =automatically expands one file at a time in a round-robin fashion to =accommodate more data (provided that the database is set to grow =automatically). For example, a filegroup comprises three files, all set =to automatically grow. When space in all files in the filegroup is =exhausted, only the first file is expanded. When the first file is full, =and no more data can be written to the filegroup, the second file is =expanded. When the second file is full, and no more data can be written =to the filegroup, the third file is expanded. If the third file becomes =full, and no more data can be written to the filegroup, the first file =is expanded again, and so on."
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andy Svendsen" <andy_mcdba@.yahoo.com> wrote in message =news:#mvLcGgfDHA.2236@.TK2MSFTNGP12.phx.gbl...
Tables only can be assigned to a specific filegroup. When you right =click
the table name in Enterprise Manager and script it, it will tell you =what
file group it is on
eg
CREATE TABLE ....
( ...
)
ON PRIMARY
The above example is the primary group.
What SQL does is when you add objects to a filegroup is fill up the =first
file, then move to the next, move to the next after that second one =was
filled and so on. That is why you see the uneven file distribution. = You'll
have to dig around in SQL books online, but it should be in there =somewhere.
I learned this studying to recertify on SQL 2000.
-- ***********************************
Andy S.
andy_mcdba@.yahoo.com
***********************************
"Kevin" <kevin@.noemail.com> wrote in message
news:%23YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> Is there a way to see which table belong to which file of a file =group? I
> have a filegroup of 10 files and they files are not evenly =distributed.
One
> hold 16G while the other in 3G range. Thanks!
>
>
--=_NextPart_000_002C_01C37E13.7C7581E0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Could you please post the version of =SQL Server you are using?
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Eddie" =wrote in message news:ekW7MmhfDHA.3464=@.TK2MSFTNGP11.phx.gbl...
We don't have automatic growth option =turned on on any of the data files. We monitor and expand the files manually, =according to the free space in the database. So far, we had several data file expansions, but only when database population was around =80%. Neather data file was completely populated, so according to BOL, as I =understand, database engine shouldn't have started using round-robin... =Eddie
"Tom Moreau" = wrote in message news:%23FUTuqgfDHA.9=44@.TK2MSFTNGP11.phx.gbl...
In the 2nd paragraph of the BOL =extract below, it mentions what happens with file growth. Basically, as long as =you stay ahead of the file growth yourself, it will do proportional =fill. However, once everything fills, you start expanding and filling in a round-robin fashion.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Eddie" =wrote in message news:#EoHZigfDHA.3528=@.tk2msftngp13.phx.gbl...
And if it is so, how come that I get significantly higher usage on one of my disks (10 times), if I have =only one filegroup, which is spread across 7 hard disks (each of them has one =or two data files)? It seems to me that practically, database engine uses =diferrent strategy...
Eddie
"Tom Moreau" = wrote in message news:eammxQgfDHA.128@.t=k2msftngp13.phx.gbl...
Not quite. It uses a =proportional fill strategy. From the BOL:

"Filegroups use a proportional fill strategy across =all the files within each filegroup. As data is written to the filegroup, =Microsoft=AE SQL Server=99 2000 writes an amount proportional to the free space =in the file to each file within the filegroup, rather than writing all the data =to the first file until full, and then writing to the next file. For =example, if file f1 has 100 megabytes (MB) free and file f2 has =200 MB free, one extent is allocated from file f1, two extents from =file f2, and so on. This way both files become full at about the =same time, and simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server automatically expands one file at a time in a round-robin fashion to = accommodate more data (provided that the database is set to grow automatically). For example, a filegroup comprises three files, all =set to automatically grow. When space in all files in the filegroup is =exhausted, only the first file is expanded. When the first file is full, and no =more data can be written to the filegroup, the second file is expanded. =When the second file is full, and no more data can be written to the =filegroup, the third file is expanded. If the third file becomes full, and no more =data can be written to the filegroup, the first file is expanded again, and =so on."
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Andy Svendsen" =wrote in message news:#mvLcGgfDHA.2236=@.TK2MSFTNGP12.phx.gbl...Tables only can be assigned to a specific filegroup. When you right clickthe table name in Enterprise Manager and script it, it will =tell you whatfile group it is onegCREATE TABLE =....( ...)ON PRIMARYThe above example is the primary group.What SQL does is when you add objects to a filegroup =is fill up the firstfile, then move to the next, move to the next after =that second one wasfilled and so on. That is why you see the =uneven file distribution. You'llhave to dig around in SQL books =online, but it should be in there somewhere.I learned this studying to =recertify on SQL 2000.-- =***********************************Andy S.andy_mcdba@.yahoo.com****=*******************************"Kevin" =wrote in messagenews:%23YdaQAgfDHA.5=76@.tk2msftngp13.phx.gbl...> Is there a way to see which table belong to which file of a file group? I> have a filegroup of 10 files and they files =are not evenly distributed.One> hold 16G while the other in 3G range. Thanks!>>

--=_NextPart_000_002C_01C37E13.7C7581E0--

Monday, March 12, 2012

Filegroup Question

Filegroup Question

Friday, March 9, 2012

Filegroup & tables

Format Alignment

Blog Archive

About Me