Michael Maddison
michael at ddisolutions.com.au
Sun Jan 23 18:24:51 CST 2005
John,
I've got a PPT presentation from Unisys that talks about filegroups and
a lot of other interesting tidbits to do with how best to
set up the physical database. If you like I can send it direct to
jwcolby at colbyconsulting.com?
cheers
Michael M
Can a database be spread over more than one filegroup? If so, then I
create a file group that is just a single file, i.e. filegroup1 is
datafile1,
filegroup2 is datafile2 etc. Then I can intentionally move a table to a
specific filegroup and get it in a specific file (disk)
John W. Colby
www.ColbyConsulting.com
Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Billy
Pang
Sent: Sunday, January 23, 2005 4:51 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Placing indexes in a specific file
Hi John:
To move a table from one filegroup to another, from EM, go into design
view of the table, bring up properties. on the first tab ("tables"),
the third last dropdown list is table filegroup. You can use this
dropdown list to move table from one filegroup to another. When you
move tables from one filegroup to another, this is what EM does is
recreate a temporarily copy of
the table in the target filegroup, copy over data, drop old table and
rename
new one.
You can also move indexes from one filegroup to another via same way
(ie.
table properties, indexes/keys, index filegroup dropdown list).
Filegroups is just a way of organizing your data files. Filegroups
consist of physical data files. AFAIK, I don't think it is possible to
put data of a table in a specific physical data file (you can only put
table into file group. sql server does the rest). For performance
reasons, in a single filegroup, it is recommended that your physical
data files are spread out over multiple disks. That way all disks can
work simultaneously at once to get your data for a single table.
Not sure what is the question "Do I need to create a new file on a new
drive, make it a new file group?, Then a new file on a second disk,
making it a different filegroup? Repeat until all files are in a
different filegroup? This all seems terribly cumbersome." I believe
that you can add
more new data files (on a new disk) to an existing filegroup if you run
out of space on the disks on the existing data files.
I don't remember how SQL Server allocates the data to the physical data
files. (ie. if filegroup has multiple data files, when you insert a
record into table within that filegroup, which data file is the record
physically stored in?). I don't have my sql server book with me so
maybe somebody else
knows.
HTH
Billy
>From: "John W. Colby" <jwcolby at colbyconsulting.com>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: dba-sqlserver at databaseadvisors.com
>Subject: RE: [dba-SQLServer] Placing indexes in a specific file
>Date: Sun, 23 Jan 2005 15:49:17 -0500
>
>Billy,
>
>Thanks for the response. Unfortunately I have already created most of
>the tables, and in fact already moved the data into most of the tables.
>I created the tables by simply opening the big table in design view,
>selecting the fields I wanted in the smaller table, copying the fields
>to the paste buffer, creating a new table and pasting the fields in to
>the new table and saved. I then opened QA, did a "Script object to new
>window as SELECT" , then "Script object to new window as INSERT", cut
>the select statement and pasted it into the VALUES clause of the
>insert, and finally ran the insert statement. Quick and easy. The
>data gets moved from the BigTable to the new table in a couple of hours
>(depending on how many I run simultaneously).
>I then go in to each small table and build the indexes manually. Close
>and save the table and the tables are built.
>
>As you can see I never used a SQL statement for any of this. It is
>very fast to do it manually, and that process of building the smaller
>tables is almost complete. I do still have to index about half of the
>smaller tables, but because of the data in them I don't create
>clustered indexes.
>
>Thus I need to know if I can take an existing table and move it to a
>specific file. I suppose I could a INSERT INTO NEWSmallTable SELECT *
>FROM SmallTable ON NEWFileGroup? That would copy the existing small
>tables I have created to the new file?
>
>I must admit that I am confused about the concept of FileGroup. AFAICT
>all of the files I currently have are in one file group. Thus using an
>On FILEGROUP would only get it into the set of 4 files, not into a
>specific file of that file group.
>
>Do I need to create a new file on a new drive, make it a new file
>group?, Then a new file on a second disk, making it a different
>filegroup? Repeat until all files are in a different filegroup? This
>all seems terribly cumbersome.
>
>John W. Colby
>www.ColbyConsulting.com
>
>Contribute your unused CPU cycles to a good cause:
>http://folding.stanford.edu/
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Billy
>Pang
>Sent: Sunday, January 23, 2005 2:57 PM
>To: dba-sqlserver at databaseadvisors.com
>Subject: RE: [dba-SQLServer] Placing indexes in a specific file
>
>
>Hi John:
>
>To improve disk I/O performance, place data of table and its indexes in
>separate data files (because less contention for disk resources given
>the way data is accessed on disk).
>
>The syntax for creating index on specific filegroup is:
>
>"BOL Syntax"
>CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
> ON { table | view } ( column [ ASC | DESC ] [ ,...n ] ) [ WITH <
>index_option > [ ,...n] ] [ ON filegroup ]
>
>For example:
>create index ix_myindex on products(product_name) on
>secondary_file_group;
>
>To store the table in a specific data file, use the ON filegroup at the
>end of CREATE TABLE statement or create a clustered index of that table
>in the target filegroup. The data of a table resides in the same
>filegroup as its clustered index.
>
>Note: each db has default filegroup. If you don't specify specific
>filegroup when you create index/table, then that is where the
>index/table will reside.
>
>Always create your clustered index first before creating your
>nonclustered indexes of a table. This is because physical key in
>nonclusterd indexes is composed of the clustered index key. If you
>create nonclustered index first, and then clustered index, the db will
>need to rebuild existing nonclusterd indexes of that table (argh!).
>
>Sometimes an existing index on a table helps speed up creation of other
>indexes on the table.
>
>HTH
>
>Billy
>
> >From: "John W. Colby" <jwcolby at colbyconsulting.com>
> >Reply-To: dba-sqlserver at databaseadvisors.com
> >To: dba-sqlserver at databaseadvisors.com
> >Subject: RE: [dba-SQLServer] Placing indexes in a specific file
> >Date: Sun, 23 Jan 2005 13:47:45 -0500
> >
> >On the same note, is it possible to cause specific tables to be
> >placed in specific files?
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com