After you have created your tables, and stored procedures you will have a basic idea of what queries are going to be running against your database. If you haven’t already done so, its now time to start adding indexes to your database. The catch with index is that there is such a thing as to much or a good thing. As you add more and more indexes to your database your INSERT, UPDATE and DELETE commands will being to slow down, as each time you insert a record the insert is committed to the table, as well as to each index on the table.
Because of this you want to only add indexes which are needed, as well as to view your indexing on the database on a regular basis to ensure that the indexes which are on the system are actually needed. This becomes extremly important as you add more and more transaction load to the database.
The syntax for adding an index is pretty straight forward.
CREATE INDEX IndexName ON Schema.TableName (Column1, Column2, Column3, ...) INCLUDE (Column4, Column5, Column6, ...) WITH (FILLFACTOR=80%, ONLINE=OFF, MAXDOP=1) ON FileGroup
Column placement is critical is getting the most benefit from your indexes. If the columns are in the index in the wrong order, then you will have to preform Index Scans instead of Index Seeks which will not give you the performance boost that you are looking for. (Look for a post early in December on reading execution plans.)
When you first start creating indexes, getting the column order correct will be a lot of trial and error. As you spend more time working with indexes you will begin to get the column order correct on the first or second try. Typically you will want the columns which are being filtered by the highest magnitude as the first column in the index, followed by the columns with the next highest magnitude of filtering and so on.
In SQL Server 2005 you can add what are called included columns. Included columns are columns which are included in the index, but are not indexed within the index. These columns can not be used for filtering by the index, but they can be returned from the index to the client application. This gives you the ability to return these values without having to go to the clustered index or the table. This saves you IO time as you already have the data page loaded from disk.
You can then specify the fill factor, online build, maxdop as well as other settings.
The fill factor tells the SQL Server engine how much space to leave empty in the index. This allows you add additional values to the index after it has been created so that you do not have to move a large amount of data to fix the data into the index. Columns which are identity values will need a high fill factor as there will not be any inserts in the middle of the index, while indexes on the customers name will need a much lower fill factor as there will be a great deal of inserts into the middle of the index.
The online flag allows you to create the index without putting an exclusive lock on the table. If you have a large table, in a 24×7 enviroment you will want to use this switch to ensure that you can keep using the table while you are creating the index. This switch can only be set to ON when you are using the Developer or Enterprise edition of the SQL Server engine. This setting is only avaiable in SQL Server 2005 and newer.
Maxdop tells you how many CPUs will be used when creating your index. By default SQL will decide how many indexes to use between 1 and the total number of CPUs in the system. Normally you will want to leave this setting alone. If you are creating a large index on an active system using the online setting, you may want to set this setting to 1 or 2 so that you leave plenty of CPUs available for other processes to use.
The file group specifies which logical file group (and via that which physical file) the index will be created in. For larger more complex system you will want to create your indexes on a different file group than your tables. The catch is that the clustered index must be created on the same file group as the physical table. Only the nonclustered indexes can be created on a different file group. If you attempt to build the clustered index on a different file group the physical table will be moved to that file group as well.
I hope that you find this information useful when creating your indexes.
Denny