SQL Server Indexes best practices

SQL Server Indexes best practices

Below are some of the SQL Server Indexes best practices or points to keep in mind while creating SQL Server Indexes.

1. Although indexes can make querying a database faster, they slow changes to the database. The more write-heavy a table is, the more careful you need to be when you add you indexes. Indexing isn't a one time event, though. As your database grows, you will need to reevaluate your indexing strategy. Indexes will need to be periodically rebuilt to ensure best performance. Changes to client applications, database design, or even your server's hardware will change your indexing strategy.

2. Keep indexes as narrow as possible. In other words, use the absolute minimum number of columns necessary to get the effect you want. The larger the composite index, the harder SQL Server will work to keep it updated and to use it in queries.

3. The first column you specify should be as unique as possible, and ideally should be the one used by most queries’ WHERE clauses.

4. Composite indexes that are also covering indexes are always useful. These indexes are built from more than one column, and all the columns necessary to satisfy a query are included in the index, which is why the index is said to cover the query.

5. Avoid using composite indexes as a table’s clustered index. Clustered indexes don’t do as well when they’re based on multiple columns. Clustered indexes physically order the table’s data rows and work best when they’re based on a single column. If you don’t have a single useful column, consider creating an identity column and using that as the basis for the clustered index.
[Source: The Definitive Guide to Scaling Out SQL Server 2005 Edition By Realtimepublishers.com]

Some other popular best practices while creating SQL Server Indexes include the following:

1. Use clustered indexes on primary keys, by default.
2. Index on search columns.
3. Use a database-level fill factor.
4. Use an index-level fill factor.
5. Index on foreign key columns.

[Source: Pro SQL Server 2012 Practices By Chris Shaw, Grant Fritchey, Carlos Bossy, Jeremy Lowell, Gail Shaw, Jesper Johansen, Mladen Prajdi, Wendy Pastrick, Kellyn Pot'Vin, Jason Strate, Herve Roggero, TJay Belt, Jonathan Gardner, Glenn Berry, Bradley Ball, Jes Borland, Ben DeBow, Louis Davidson]

More on SQL Server Indexes best practices

Index use by SQL Server depends on a number of factors, including, but not limited to: the query construction, referenced tables in the query, referenced columns, number of rows in the table, data distribution, and the uniqueness of the index column(s) data. The following are some basic guidelines to keep in mind when building you index strategy:

1. Add indexes based on your high-priority and high-execution count queries. Determine ahead of time what acceptable query execution durations might be based on your business requirements.

2. Don’t add too many indexes at the same time. Instead, add an index and test the query to see that the new index is used. If it is not used, remove it. If it is used, test to make sure there are no negative side effects to other queries. Remember that each additional index adds extra overhead to data modifications to the base table.

3. Unless you have a very good reason not to do so, always add a clustered index to each table.

4. A table without a clustered index is a heap, meaning that the data is stored in no particular order. Clustered indexes are ordered according to the clustered key and its data pages reordered during an index rebuild or reorganization. Heaps, however, are not rebuilt during an index rebuild or reorganization process, and therefore can grow out of control, taking up many more data pages than necessary.

5. Monitor query performance over time. As your data and application activity changes, so too will the performance and effectiveness of your indexes.

6. Fragmented indexes can slow down query performance, since more I/O operations are required in order to return results for a query. Keep index fragmentation to a minimum by rebuilding and/or reorganizing your indexes on a scheduled or as-needed basis.

7. Select clustered index keys that are rarely modified, highly unique, and narrow in data type width. Width is particularly important because each nonclustered index also contains within it the clustered index key. Clustered indexes are useful when applied to columns used in range queries. This includes queries that use the operators BETWEEN, >, >=, <, and <=. Clustered index keys also help reduce execution time for queries that return large result sets or depend heavily on ORDER BY and GROUP BY clauses. With all these factors in mind, remember that you can only have a single clustered index for your table, so choose carefully.

8. Nonclustered indexes are ideal for small or one-row result sets. Again, columns should be chosen based on their use in a query, specifically in the JOIN or WHERE clause. Nonclustered indexes should be made on columns containing highly unique data. Don’t forget to consider using covering queries and the INCLUDE functionality for non-key columns.

9. Use a 100% fill factor for those indexes that are located within read-only filegroups or data- bases. This reduces I/O and can improve query performance because fewer data pages are required to fulfill a query’s result set.

10. Avoid wide index keys. Always test narrower composite keys in favor of larger indexes.

11. Try to anticipate which indexes will be needed based on the queries you perform — but also don’t be afraid to make frequent use of the Database Engine Tuning Advisor tool. Using the Database Engine Tuning Advisor, SQL Server can evaluate your query or batch of queries and determine what indexes could be added (or removed) in order to help the query run faster.
[Source: SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach By Jason Brimhall, David Dye, Timothy Roberts, Wayne Sheffield, Jonathan Gennick, Joseph Sack]

Few other sources for SQL Server Indexes best practices

1. MS Sql Server Indexes
2. Best practices for creating indexes
3. SQL Server Indexing best practice (SQL Server 2008)
4. Efficient SQL Server Indexing by Design
5. SQL Server Performance: Indexing Basics
6. SQL Indexing and SQL Performance Part 4: Design Considerations
7. SQL Server Indexing Best Practices: Stairway to SQL Server Indexes Level 15
8. Five Indexing Tips to Improve SQL Server Performance
9. Best Practices for Creating Indexes
10. 5 Things About Fillfactor