Indexing General

July 15, 2009 – 11:43 pm

An index is a database structure designed to facilitate faster data retrieval Indexes provide a way for SQL Server to organize pointers to the data required An index in a database works the same way as an index in a reference book.

Why Index?
Speed
Without index SQL Server must perform a table scan or read every row in a table
Speed up query joins between tables

Indexing Architecture
SQL Server indexes are stored as B-Tree (Balanced Tree) structures
There are three levels of a B-Tree:
Root Level
Leaf Level
Intermediate Level
A B-Tree structure is built for every index defined in SQL Server

Types of Indexing
There are two types of SQL Server indexes
Clustered
Non-Clustered
Both are built upon B-Tree structures

Clustered Index
The data is physically stored in the sorted order
The leaf pages are the actual data
Only one clustered index is allowed per table
Generally offers better performance than non-clustered index

Non-Clustered Index
Non-clustered indexes create a logical order and therefore, the leaf page merely points to the actual data Create up to 249 Non-clustered indexes per table. Non-clustered index are larger than clustered indexes because of the extra level of leaf pages

Index Creation
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
Indexes can be created at the same time table is created or at a later time
Create clustered first before adding non-clustered indexes.

When to use Clustered vs. Non-Clustered index
Clustered
Columns that are used in range queries
Columns used in ORDER BY or GROUP BY queries
Queries returning large result sets
Static look up tables with large data set
Non-Clustered
Columns used in aggregate functions
Foreign Keys
Queries returning small result sets
Primary keys that are sequential ID keys, such as identity columns

What NOT to index
Tables with a small number of rows
Might be more efficient to do table scan
Columns with small range of values
For example: (yes/no, 0/1, 1,2,3)
Tables with heavy transaction (insert,update,delete) activity but very few select queries run against it
Tables with indexes slow down data modifications
Columns not used in queries
If the column(s) are defined as TEXT, NTEXT, or IMAGE data types

Additional Tips
Try to create indexes on columns that have integer values
Integer values have less overhead than character values
When creating indexes, try to make them unique indexes
Columns used in joins should have an appropriate index
Don’t accidentally add the same index twice on a table
Don’t over index your OLTP tables
Remove unused indexes.

Re-indexing
Periodically (weekly or monthly) perform a database reorganization on all the indexes on all the tables in your database
Frequency depends on transaction activity in database
This will rebuild the indexes so that the data is no longer fragmented
Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server’s performance
Use DBCC DBREINDEX(@TableName,’ ‘,90) for specific tables
Use Update Statistics Command
after bulk insert/update to table

Index Tuning Wizard
Powerful tool designed to help you identify existing indexes that aren’t being used
Recommends new indexes that can be used to help speed up queries
Excellent starting point for indexing if you aren’t sure what to index

Index Tuning Wizard 
Don’t blindly accept every recommendation made by the wizard
Sometimes the wizard will not recommend an index
Review each recommendation carefully, sometimes rewriting a query will do more good than adding an additional index

Conclusion
Indexing properly can dramatically improve overall query and application performance
There are many factors to consider when deciding to index
Index wisely