SQL Server Index Checklist

By: Jeremy Kadlec   |   Updated: 2008-03-11   |   Comments (2)   |   Related: 1 | 2 | 3 | > Indexing

Problem

Indexing a SQL Server database in some respects is considered both an art and a science.  Since this is the case, what are some considerations when designing indexes for a new database or an existing one in production?  Are these the same types of steps or not?  Do any best practices really exist when it comes to indexing?  Where does indexing fall in the priority list from an application or production support perspective?

Solution

You have a number of good questions related to indexing a SQL Server database.  The reality is that many of the decisions are situational, although some of the decisions should be approached from a best practices perspective then evaluated as they pertain to your specific environment.  Although many best practices do exist, application, business, technology, resource and time constraints can pose significant challenges to address the need, which is the fastest data access possible.

Art vs. Science

In terms of the art vs. science question, in many respects this is true in many circumstances.  The reality is that 100% of the time there is not 1 right answer.  What I have seen and done on a number of occasions is start off with the industry best practices approach and then based on the business, technology, application, etc. needs begin to figure out the best practices for the environment.  A single solution to solve all problems is not feasible, but the best solution is to be knowledgeable about the options available in SQL Server, have an open mind, test a variety of options, then select the best option based on the results not the theory or conjecture in that particular situation.

In terms of recommendations, it is best to have an open mind and a clear understanding of the data access code.  Hopefully this will enable you to choose some non-traditional options vs. traditional options to see what indexing strategy is going to best meet the need.  As an example, knowing that the database is accessed via a middle tier application that generates cursor based T-SQL code versus stored procedures with set based logic, could change how the database is indexed to ensure the lowest cost for your queries.  Understanding these intricacies is where the art portion of the equation can be essential.

New vs. Existing Systems

In some respects the process to identify and build the correct indexes is the same between a new versus existing database.  One key differentiator is a new system typically has a clean slate where an efficient database can be designed and built.  When you compare this to an existing system, they typically have a great deal of baggage and application intricacies may exist.  The reality is that these items are difficult to change without impacting a number of different applications.

Let's take a look at the process of identifying and\or correcting SQL Server indexes:

• Review the business, technical, performance, etc. requirements.
• Review the hardware to validate it is generally sufficient to meet the needs.
  • For example, make sure a 2 CPU machine with 1 GB of memory and a few disks is not supporting terabytes of data and thousands of users.  If this is the case, the hardware is probably inadequate in the first place.
  • Additional information - Hard Drive Configurations for SQL Server
• Review the database design for primary keys, foreign keys, correct data types, reasonable usage of NULL, etc.
  • Additional information - SQL Server Code Review Checklist
• Validate that the system is not overloaded or taxed from a design perspective.
  • For example, the database design is not supporting OLTP, batch, reporting and analytical processing all via a single database design.
• Review the current indexes to understand what is currently in place, to include:
  • Clustered indexes
  • Non clustered indexes
  • XML indexes
  • Full Text Indexes
  • Additional information - Understanding SQL Server Indexing
• Capture the most costly code with Profiler or a third party tool to focus on the most offensive queries.
  • Additional information - SQL Server Performance Statistics Using a Server Side Trace
  • Additional information - SQL Server Performance Monitoring Tools
• Analyze the code and review the query plan to understand how the optimizer is processing the query, once this is understood start to think about various approaches to improve the query.
  • For example, if possible remove LIKE statements, remove functions in the WHERE clause, remove temp table usage, etc.
  • Additional information - Query Plans in SQL Server 2000 vs SQL Server 2005
• Take a big step back and think about what is functionally trying to be accomplished by the code and see if any new or different techniques are available to address the need.
  • Additional information - Checklist to Re-Architect a SQL Server Database
• Pull together as many reasonable options as possible to address the issue then begin to test each option and record the metrics for each approach.
  • Let the numbers speak for themselves and select the best performing options to meet the need.
  • Be sure that a change to the indexes in one query or one portion of the application will improve one area and not hurt another area.
  • Additional information - MSSQLTips Category - Testing
• Deployment
  • Once the indexes have been finalized, deploy them and continue to monitor the performance for no unexpected results.

Index Best Practices

When in comes to best practices, in some respects I consider them a set of ground rules that should be used as a means to start the design and development process as opposed to an absolute set of rules that are always correct.  With that being said, let's outline some points of reference to use as a starting point to address your indexing needs:

• Data Access - Determine how the data is being accessed (SELECT) and maintained (INSERT, UPDATE, DELETE) then index accordingly.  Keep in mind that data may not always be accessed in the same way, so you may need to prioritize or compromise to have the best net performance across the application.
• Plan - Have a process to analyze the indexes versus haphazardly reviewing tables or T-SQL code to build your indexing strategy.  Start with a functional area or based on the application flow so no tables fall through the cracks.  Keep in mind that indexes on a subset of the tables do not make up for little or no indexes in another portion of the database design.
• Index selection - As a starting point, be sure to identify a clustered index and one or more non clustered indexes for each table.  During the testing and validation phases, fine tune or eliminate the indexes based on the data access.
• Covering indexes - Do not be afraid to have an index with numerous columns to improve the data access and potentially avoid bookmark lookup operations.  This topic is well covered in the Avoid Index Redundancy in SQL Server Tables tip.
• ASC vs. DESC order - The order of the indexes can make a significant difference in the data access for covering indexes.  Check out the Building SQL Server Indexes in Ascending vs Descending Order tip as an good point of reference.
• Fill Factor - Depending on how the data is maintained (INSERT, UPDATE, DELETE), the fill factor could significantly impact the performance, page splits and storage requirements.  What is necessary is to select the appropriate fill factor as outlined in the Fragmentation Station - Stop #4 - How to avoid it blog entry.
• Balance - Indexes are not free when it comes to having SQL Server maintain and support the storage.  Be sure to have the needed indexes, but not excessive or repeating indexes as outlined in the Avoid Index Redundancy in SQL Server Tables tip.

Indexing Priorities

When it comes to new development projects versus production support it is hard to have an absolute answer where indexing falls into the priority list.  In terms of a new development project, it would be wise to spend equal time on building the database design, the indexing strategy and the data access code.  Without it, the application can become a maintenance nightmare requiring a great deal of attention in the production environment.  In many respects this is easier said than done if the database is just considered a black box that is expected to run forever without any attention.  If you are faced with that issue, then it is necessary to change that mentality first and make sure the time, energy and resources properly address the database needs moving forward.

For production applications, reviewing the indexes and query plans should be a high priority when a performance problem is occurring.  In many respects changing or adding indexes should be one of many items that are reviewed, analyzed, tested and deployed as opposed to considering indexes a magic bullet to resolve performance problems.

Another important consideration with the indexes is that as the application changes, the indexes may need to change as well.  Do not consider the indexes stagnant, but rather review the code with index implications in mind.  So as the application changes do not be afraid to change, test and deploy an improved indexing strategy.

Next Steps

• In terms of SQL Server databases performance, indexes can make or break an application.  I have personally seen a new and simple indexing strategy breath the life into an application and give users confidence in the application.
• Although indexes are not always a winner when it comes to a quick performance fix, they should be one aspect of the database that is reviewed to see if a quick fix can be applied to help the application.  Keep in mind that a number of quick fixes can cause a big mess in the long term, so be sure to balance the needs and build a long term solution.
• With this being said, if the proper time, energy and resources are originally spent building the application, then the need for a quick fix should be minimal.
• Check out the MSSQLTips index category for related indexing tips.

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips