Indexes The Basics Kathi Kellenberger Good things come
Indexes: The Basics Kathi Kellenberger
Good things come in small packages…
Two Types of Tables Heaps B-Trees
Heap M S D X A T F
B-Tree M T D A F S X
Find the Number Game
Clustered Index Structure M T D A A D F F S M S Non-Leaf Levels X T X Leaf Level Non-leaf levels contain the cluster key Leaf levels contain the data pages: cluster key + non-key columns
The best cluster key is ever-increasing M S D X A T F
The best cluster key is narrow and unique
The Best Clustered Key is Static CHANGES
Non-Clustered Indexes
Non-Clustered Indexes
Non-clustered Index Structure M T D A A D F F S M S Non-Leaf Levels X T X Leaf Level Non-leaf levels contain the Index Key Leaf levels contain the Index Key, Pointer to table row and Included Columns
Column Order Is Important! Last. Name First. Name City
Key Lookups • When the index just isn’t enough
Covering Indexes
Best Practices • Index foreign keys • Index keys used in searches • Avoid duplicate indexes • Avoid indexes that are rarely used – unless there is a business reason • DO NOT BLINDLY FOLLOW RECOMENDATIONS
Index Maintenance • • Maintenance Wizard • • Reorganize Rebuild Scripts • • Rebuild at 30% fragmentation Reorg > 10% and < 30% Ignore small tables Record what is done
Resources • Glenn Berry Blog: http: //sqlserverperformance. wordpress. com/ • Expert Performance Indexing for SQL Server 2012 by Jason Strate (Apress) • Ola Hallengren maintenance solution: https: //ola. hallengren. com/ • Duplicate index script: http: //www. sql-server-performance. com/2013/findduplicate-indexes-on-sql-server/
- Slides: 20