What are some indexing dos?
- No system is stagnant, so do not fear making index changes to support a usage shift.
- Index columns with highly selective (different) data. If the data selectivity is low, SQL
Server cannot derive a great deal of value from the index.
- If you have a very large database (VLDB), split the data and clustered index from the
nonclustered indexes into a separate file group on separate physical disks.
- Approach indexing from a holistic perspective to ensure that indexes that are beneficial in one
portion of the application are not detrimental in another portion.
- Balance the number of indexes per table to give SQL Server less work to do when performing
transaction processing. That would require less overall storage to support it while still allowing
for efficient processing in terms of time and I/O.
- Balancing also helps ensure that new indexes do not benefit one portion of the application and unintentionally strangle another. Test the indexes thoroughly and have a second
Requires Free Membership to View
- pair of eyes review
the indexes to double check that a change in one place does not adversely impact another portion of
the application.
- Review query plans during the testing phase to ensure the index will improve the query time and
resource use as expected.
- Select an appropriate fill factor for each index. If the data has a minimal amount of changes
to the middle of the table, configure the indexes to have a high fill factor, i.e., closer to 100%,
which will save on the storage needed. If the data has many changes to the middle of the table,
select a lower fill factor, i.e., 65% to 85%, so that as data is added to a page, page splitting is
minimized until the indexes are rebuilt.
- Issue the DBCC SHOWCONTIG command on a regular basis to determine the fragmentation of the
table, index or data. Based on the fragmentation level, rebuild the indexes on a regular
basis.
- If indexes are experiencing a great deal of fragmentation in a short period of time, review how
the data is inserted, updated and deleted to determine if you need a lower fill factor. Also,
determine if the code can be changed to reduce the amount of fragmentation.
- Capture a performance
baseline as indexes are created, removed and/or rebuilt to establish the performance impact for
each of these operations.
- Follow a change
management process with testing and deployment of new indexes. Make sure you document the
overall change.
- Perform database maintenance on a weekly, monthly or quarterly basis. Do not expect your SQL Server to run like a finely tuned machine unless you perform maintenance.
What are some indexing don'ts?
- Avoid hot spots on your table. A hot spot occurs when all of the transactions are accessing the
same location of the table (for example, the end of the table) and causing contention issues.
- Do not use index hints to override the SQL Server optimizer index selection for the query
unless all of the options have been exhausted.
- Avoid page splitting, which happens when no room is available for a new row on an existing data
page. When this occurs, SQL Server takes half of the data and moves it to a new page. The net
result is the original page has the first half of the data and the new page has the second half of
the data. This is a very resource-intensive process that SQL Server goes through to fit the row and
continue processing.
- Do not build or maintain duplicate indexes unnecessarily.
The art and
science of SQL Server indexing
Home: Introduction
Part 1: Indexing
Q&A
Part 2: SQL Server 2000 indexing dos and
don'ts
Part 3: Tricks for using
the Index Tuning Wizard
ABOUT THE AUTHOR
Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company
delivering professional services and product solutions for Microsoft SQL Server.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation