Index Design Basics

  Designing efficient indexes is paramount to achieving good database and application performance. The selection of the right indexes for a database and its workload is a complex balancing act between query speed and update cost. Narrow indexes, or indexes with few columns in the index key, require less disk space and maintenance overhead. Wide indexes, on the other hand, cover more queries. You may have to experiment with several different designs before finding the most efficient index. Indexes can be added, modified, and dropped without affecting the database schema or application design. Therefore, you should not hesitate to experiment with different indexes.

Index Design Tasks

1.Understand the characteristics of the database itself. OLTP database or OLAP database.
2.Understand the characteristics of the most frequently used queries.
3.Understand the characteristics of the columns used in the queries.
4.Determine which index options might enhance performance when the index is created or maintained.
5.Determine the optimal storage location for the index.For example, storing a nonclustered index on a filegroup that is on a different disk than the table filegroup can improve performance because multiple disks can be read at the same time.