We all know indexes can speed up queries. But why? And how? Why is “index on every column” not a good indexing strategy, why is your shiny new index not used, and why can adding indexes even hurt performance? The key to answering this is understanding how SQL Server lays out your data on disk, how this changes when you add an index, and how SQL Server uses these indexes to find the information required for your queries.

The first part of this presentation will provide in-depth insight in how heaps, clustered indexes, and nonclustered indexes are organized on disk; and how they are used for scans, seeks, and lookups. The second part will then continue on advanced features, such as included columns, indexed views, and filtered indexes. You will see the tools SQL Server itself hands you to help identify just the right subset of indexes, and their limitations and weaknesses. And finally, you will get a stunning demonstration of just how bad you can HURT performance by creating too many indexes.

Armed with this information, you should be able to find just the right set of indexes for your workload.
Presented by Hugo Kornelis at SQLBits XIV