Index This: All About SQL Server Indexes

Indexing in SQL Server can make all the difference in your performance— great indexing can make your application nimble and fast. Poor indexing can slow your whole SQL Server to a terrifying crawl.

Here are links to our most popular posts on indexing in SQL Server. These posts have diagrams, videos, and scripts to help you get your B-Plus trees into fighting shape.

The D.E.A.T.H. Index Tuning Process

Relax – it’s nothing to be afraid of. When you’re tuning indexes on an existing database, work through them in this order:

  • Dedupe – reduce overlapping indexes
  • Eliminate – unused indexes
  • Add – desperately needed missing indexes
  • Tune – resource-intensive query plans
  • Heaps – usually need clustered indexes

The rest of this page gives you resources on how to follow that process, why each step is important, and how to use our free tools to do it.

Best Practices for Clustered and Nonclustered Indexes

SQL Server Index Terms – Clustered indexes, nonclustered indexes, B-trees, and covering indexes explained with examples and diagrams.

Heaps: Tables Without a Clustered Index – Do you know WHY it’s a best practice to create clustered indexes on tables in SQL Server, and what can happen if you don’t? Kendra Little shows you the weirdness that can happen if you forget a clustered index.

Stop Worrying About Fragmentation – Brent explains internal vs external fragmentation, how it hurts SQL Server performance, how to fix it short term, and how to fix it for good.

Rebuild or Reorganize? How to Design Index Maintenance – Kendra explains the difference between index rebuild and reorganize commands and how to select which is right for you.

Disable Indexes, Not Drop Them – How to use ALTER INDEX to disable an index, and then enable it again with REBUILD. With this technique, index metadata and statistics are persisted.

Index Hints: Helpful or Harmful? – Jes explains why bossing the query optimizer around may or may not be a great idea!

sp_BlitzIndex® And Other Index Tuning Tools

Our sp_BlitzIndex® procedure is a free tool to help you find out what’s crazy in your SQL Server indexes. It diagnoses duplicate indexes, unused indexes, missing indexes, incorrect fill factors, unhealthy clustered indexes, and much much more. Download the free sp_BlitzIndex® stored procedure and watch an 8 minute video on how to install and use it.

How to Master Index Tuning in One Step – Kendra says index tuning is complicated, but you can become great at it with this tip.

Dude, Who Stole My Missing Index Recommendation? – Will using an index hint prevent you from getting a missing index request? Do full vs trivial optimizations make a difference?

Indexing Tutorial Videos – our free YouTube videos about indexing mistakes, filtered indexes, and how SQL Server stores data.

Advanced Index Tuning Tips and Tricks

Why SQL Server Won’t Recommend a Clustered Index – When you look at missing index requests, it’s always important to remember one of the biggest things: these missing index requests won’t ever ask for or recommend a specific clustered index. Kendra Little explains why.

What you Can and Can’t Do With Filtered Indexes – Learn the basic features and limitations of filtered indexes.

Filtered Indexes and Dynamic SQL – Jeremiah shows the trouble with parameters and filtered indexes, and how dynamic SQL can help.

Indexing for Deletes – When you first start tuning it’s easy to focus on indexing for select statements. Index tuning requires even more trickery and cleverness, however. Learn more in Jeremiah Peschka’s article.

Avoiding the “Maximum Key Length is 900 Bytes” Error Using Hash Keys – If you need to index a lot of columns on a table, you can use a computed field for equality checks.

DBA Darwin Awards: Index Edition – Could you be making a newbie indexing mistake without knowing it? Find out in Brent Ozar’s video

How to Decide if You Should Use Table Partitioning – Are you thinking about table partitioning? Learn about partitioned indexes and what to consider in Kendra Little’s post.

Want to be an Indexing Pro? We’ve got training!

Online video training: For $299 you can get 18 months of access on your desktop, laptop, iPad, or iPhone to more than six hours of video training on SQL Server indexes, chock full of demo scripts and quizzes. Get more info or buy it now!