Billy Pang
tuxedo_man at hotmail.com
Sun Jan 23 21:04:35 CST 2005
A database can have more than one filegroup. In EM, go to properties of
user database. There is filegroups tab. Type name of new filegroup in
space provided.
>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 18:06:49 -0500
>
>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
>