Tips and tricks of SQL Server clustered indexes

Recently, I got my hands dirty on SQL Server as we took a .NET migration project from one of our partners and we basically (unofficially) need to take care of everything. Argh.. No choice I need to get my hands dirty on SQL Server. A really tough time for me as I’m typically a programming type of people. Now I need to do performance tuning on SQL Server. Anyway, after spending the whole week studying SQL Server, I discovered that correct usage of clustered indexes will increase the performance of queries in SQL Server dramatically. I think would be worthwhile I blog about clustered indexes (when to use, when not to use, and lastly some considerations of using clustered indexes).
 
When to Use a Clustered Index
Because you can have only one clustered index per table, you must ensure that you use it to achieve the maximum benefit possible. Before you create a clustered index, you need to understand how your data will be accessed. Because a clustered index determines the order in which SQL Server stores the table’s data rows, clustered indexes are more suited to certain types of data and usage patterns.
 
Clustered indexes are most effective when used to support queries that do the following:
 
1. Return a range of values by using operators such as BETWEEN, >, >=, <, and <=. Because the table data is physically stored in index order, after the row with the first value is found by using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent.
 
2. Return data sorted using the ORDER BY or GROUP BY clause. An index on the columns specified in the ORDER BY or GROUP BY clause might remove the need
for the database engine to sort the data because the rows are already sorted. This improves query performance.
 
3. Return data combined by using JOIN clauses; typically these are foreign key columns.
 
4. Return large result sets.
 
When Not to Use Clustered Indexes
Clustered indexes are not a good choice when:
 
1. The data in the indexed columns will change frequently. Changes to a clustered index mean that the entire row of data must be moved because the database engine must keep the data values of a row in physical order. This is an important consideration in high-volume transaction processing systems in which data is typically volatile.
 
2. The index keys are wide. Wide keys are composite keys of several columns or several large-size columns. All nonclustered indexes use the key values from the clustered index as lookup keys. Any nonclustered indexes defined on the same table will be significantly larger because the nonclustered index entries contain the clustering key and also the key columns defined for that nonclustered index.
 
Considerations for Using Clustered Indexes
When you define a clustered index, you should define the index key with as few columns as possible. Keeping your clustered key value small increases the number of index rows that SQL Server can place on an index page and decreases the number of levels that must be traversed. This minimizes input/output (I/O). Also, consider columns that have one or more of the following attributes:
 
1. Are unique or contain many distinct values. This includes columns defined as IDENTITY because the column is guaranteed to be unique within the table. Key value uniqueness is maintained explicitly, by using the UNIQUE keyword, or implicitly, by using an internal unique identifier. If a clustered index contains duplicate values, SQL Server must distinguish between rows that contain identical values in the key column or columns. It does this by using a 4-byte integer (uniqeidentifier value) in an additional system-only uniqeidentifier column. These unique identifiers are internal to SQL Server and are not accessible to the user.
 
2. Are used frequently to sort the data retrieved from a table because this will save the cost of a sort operation whenever a query sorts results on that column.
 
3. Are frequently accessed sequentially.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: