Billy Pang
tuxedo_man at hotmail.com
Sun Jan 23 14:00:03 CST 2005
ooops.. typo in last email... instead of "BOL syntax", it should have read
"BNF Syntax from BOL"?
>From: "Billy Pang" <tuxedo_man at hotmail.com>
>To: dba-sqlserver at databaseadvisors.com
>Subject: RE: [dba-SQLServer] Placing indexes in a specific file
>Date: Sun, 23 Jan 2005 19:56:35 +0000
>
>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
>>
>