Jiansen Lu
SQL Server Index Checklist
July 24th 2008 05:25
SQL Server Index Checklist
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:
1.Review the business, technical, performance, etc. requirements.
2.Review the hardware to validate it is generally sufficient to meet the needs.
a. 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.
3.Review the database design for primary keys, foreign keys, correct data types, reasonable usage of NULL, etc.
4.Validate that the system is not overloaded or taxed from a design perspective.
a. For example, the database design is not supporting OLTP, batch, reporting and analytical processing all via a
single database design.
5.Review the current indexes to understand what is currently in place, to include:
Clustered indexes Non clustered indexes XML indexes Full Text Indexes
6.Capture the most costly code with Profiler or a third party tool to focus on the most offensive queries.
7.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.
a. For example, if possible remove LIKE statements, remove functions in the WHERE clause, remove temp table usage,
etc.
8.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.
9.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.
a. Let the numbers speak for themselves and select the best performing options to meet the need.
b. 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.
10.Deployment
a.Once the indexes have been finalized, deploy them and continue to monitor the performance for no unexpected
results.
Index Best Practices
When it 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:
1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.
7. 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.
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:
1.Review the business, technical, performance, etc. requirements.
2.Review the hardware to validate it is generally sufficient to meet the needs.
a. 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.
3.Review the database design for primary keys, foreign keys, correct data types, reasonable usage of NULL, etc.
4.Validate that the system is not overloaded or taxed from a design perspective.
a. For example, the database design is not supporting OLTP, batch, reporting and analytical processing all via a
single database design.
5.Review the current indexes to understand what is currently in place, to include:
Clustered indexes Non clustered indexes XML indexes Full Text Indexes
6.Capture the most costly code with Profiler or a third party tool to focus on the most offensive queries.
7.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.
a. For example, if possible remove LIKE statements, remove functions in the WHERE clause, remove temp table usage,
etc.
8.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.
9.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.
a. Let the numbers speak for themselves and select the best performing options to meet the need.
b. 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.
10.Deployment
a.Once the indexes have been finalized, deploy them and continue to monitor the performance for no unexpected
results.
Index Best Practices
When it 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:
1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.
7. 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.
| 62 |
| Vote |
Add To: 
subscribe to this blog
