[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?  I started this database with two 250g hard drives for the
>data and one 250g hard drive for the log file.  As I ran short of room in
>the data files (hard drives) I added a new drive and another file, then a
>fourth drive and file.  I have at this time 4 data drives each with a 
>single
>data file, and a log drive with a log file.  Each of these drives has
>nothing but the SQL Server file on it.
>
>I started out with the "big table" of 64 million records, 650 fields.  
>Since
>the business requires using almost every field in where clauses, I added a
>PK field that is an Int - Identity - Increment 1.  I then started creating
>smaller tables with anywhere from a handful to several dozen fields of
>related data.  I copied the PK and these fields (data) from the Big Table
>into the smaller tables, then set indexes on each field.  This work is not
>complete yet, but I have almost finished creating tables to hold all the
>sets of related data.  Just as an aside, it takes a few hours to pull the 
>PK
>and data out of
>BigTable and drop it into the smaller table.  I can run many of these at 
>the
>same time (do several of the smaller tables at the same time).  Setting up
>the indexes takes a few minutes to create the indexes in design view, then 
>a
>LONG time (many hours) for SQL Server to actually build the indexes.  The
>more fields I index at once the longer it takes of course.
>
>I can just imagine what the data looks like inside the four data files
>though.  I was thinking that if I could "copy" a specific table from the
>database into a new file, that the copy might "defrag" that table (get all
>the data and indexes for it in one place and contiguous).
>
>Additionally it would be good from a performance perspective to get each of
>these smaller tables into a single drive (file) so that later when I 
>started
>joining the tables for analysis (where clauses) I would spread the i/o over
>more drives.
>
>Any thoughts on this stuff?  Is it possible to determine placement of 
>tables
>within a multi-file database?  Ditto indexes?
>
>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 John W.
>Colby
>Sent: Sunday, January 23, 2005 1:22 PM
>To: dba-sqlserver at databaseadvisors.com
>Subject: [dba-SQLServer] Placing indexes in a specific file
>
>
>I believe I have seen references to placing indexes in a specific file for
>SQL Server in order to speed up the database.  Does anyone know how this
>might be accomplished (if possible)?  For my nVLDB I now have 685 gb of 
>data
>in 4 files, each file on a dedicated hard drive, i.e. nothing on that drive
>except that one database file.  I also have a dedicated drive for the log
>file.  I am reaching the point where I need to add a couple of more drives
>(and files) because I will run out of room in a few weeks.  It seems like
>now might be the time to figure out the index file thing if it is possible.
>
>John W. Colby
>www.ColbyConsulting.com
>
>Contribute your unused CPU cycles to a good cause:
>http://folding.stanford.edu/
>
>
>
>_______________________________________________
>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
>