John W. Colby
jwcolby at colbyconsulting.com
Sun Jan 23 17:06:49 CST 2005
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