MS SQL Server 2012 - DBA Articles

Overview of Index

The selection of the right indexes for a database and its workload is a complex balancing act between query speed and update cost. Indexes can be added, modified, and dropped without affecting the database schema or application design. 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.
If using an index always helped produce the best performance, the job of the query optimizer would be simple. Do not always equate index usage with good performance, and good performance with efficient index use. In reality, an incorrect index choice can cause less than optimal performance.
Poorly designed indexes and a lack of indexes are primary sources of database application bottlenecks. Designing efficient indexes is paramount to achieving good database and application performance. You may have to experiment with several different designs before finding the most efficient index. The query optimizer in SQL Server reliably chooses the most effective index in the vast majority of cases.
Index Design Guidelines
01. Understand the characteristics of the database itself. For example, is it an online transaction processing (OLTP) database with frequent data modifications, or a Decision Support System (DSS) or data warehousing (OLAP) database that contains primarily read-only data?
02. Understand the characteristics of the most frequently used queries. For example, knowing that a frequently used query joins two or more tables will help you determine the best type of indexes to use.
03. Understand the characteristics of the columns used in the queries. For example, an index is ideal for columns that have an integer data type and are also unique or nonnull columns. A filtered index is appropriate for columns that have well-defined subsets of data.
04. Determine which index options might enhance performance when the index is created or maintained. For example, creating a clustered index on an existing large table would benefit from the ONLINE index option. The ONLINE option allows for concurrent activity on the underlying data to continue while the index is being created or rebuilt.
05. Determine the optimal storage location for the index. A nonclustered index can be stored in the same filegroup as the underlying table, or on a different filegroup. The storage location of indexes can improve query performance by increasing disk I/O performance. 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.
Maximum Size of Index Keys
When you design an index that contains many key columns, or large-size columns, calculate the size of the index key to make sure that you do not exceed the maximum index key size. SQL Server retains the 900-byte limit for the maximum total size of all index key columns. This excludes nonkey columns that are included in the definition of nonclustered indexes.

* * * * *

Your Comment To AUTHOR