Indexes are critical to how fast SQL Server retrieves data, though at the same time they reduce the speed that SQL Server can write new and changed data to the database.
Properly creating and maintaining indexes will ensure that they work for -- not against -- your SQL Server environment.
Clustered and non-clustered indexes
SQL Server supports two types of indexes: clustered and non-clustered. Every database table has exactly one clustered index, and this index determines the order that data is physically stored.
For example, if you create a clustered index on a "name" column, the rows are physically ordered alphabetically by name. However, if a clustered index is not explicitly created, data is stored by the row's ordinal number, which is assigned by the order data is added to the database.
Typically, a clustered index is created on the most commonly-queried column -- often a unique ID number.
A non-clustered index works a bit differently. It is a separate structure that contains a copy of the indexed data as well as pointers back to the rest of the data.
For example, a non-clustered index on a "telephone number" column would create a copy of every number and place them in numeric order. Pointers would then relate each item in the index back to the actual row of data.
When SQL Server needs to look something up, it first finds the telephone number and then follows the pointer back to the actual data.
While this two-step process is slightly slower than using a clustered index, there is no limit to the number of non-clustered indexes you can have.
When indexes go bad
To understand how indexes can go wrong, consider what happens when changes and insertions need to be made to a physical phone book.
When a phone book is published, its pages are 100% full. If information needs to be added or amended, then supplement pages are issued and inserted in the back of the book. In order to scan through the phone book alphabetically, you need to jump between the main book and the supplement pages, which is not very efficient.
In SQL Server, this is known as index fragmentation.
If too many supplement pages are added, SQL Server might decide that the index cannot be used quickly enough to improve query performance. As a result, it will stop using it.
This is where you come in.
You need to maintain these indexes -- even if you are dealing with third-party applications where the vendor has determined what indexes to use.
Maintaining indexes
You can check the fragmentation level of an index, and Microsoft provides details for doing so. Indexes that are between 5% and 30% fragmented should be reorganized, or in other words, defragmented.
For fragmentation levels greater than 30%, you need to rebuild -- a one-step process that drops the existing index and builds a fresh one from scratch. Rebuilding is more labor-intensive than reorganizing and may impact some applications' performance.
With both steps, you can set how full you want each page in the new index to be with the fill factor key option.
For example, a fill factor of 50% would leave each page half-empty. In this scenario, although the index would take up more space on the disk, it would also have more free space for insertions and changes. As a result, the index would last longer.
Keep in mind that the larger the index, the longer it will take SQL Server to process. This is because the server has to read all the pages off of the disk, even if they are mostly empty. Therefore, specifying a fill factor of 1% would be counterproductive in many databases. In general, write-heavy databases should have a lower fill factor, while database that support mostly read traffic can have a higher fill factor.
One last key point to remember: if you rebuild the clustered index, you are physically rearranging the database rows. This means every non-clustered index will also have to be modified because the row pointers used in those indexes will change. This a very labor-intensive operation, and should only be done as part of a scheduled maintenance window.
You can also disable non-clustered indexes during the clustered index rebuild. This prevents SQL Server from attempting to update at every moment and speeds up the rebuilding operation.
 |
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
Part 1: 
Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 IT books and a speaker at technical conferences worldwide. Contact him through his website at 
