How to Think Like the SQL Server Engine
You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server. In this series of videos, you’ll learn how the SQL Server engine looks at your indexes and builds your query results.
In just one hour, you’ll learn:
- The differences between clustered and nonclustered indexes
- How (and when) to make a covering index
- The basics of execution plans
Print out this 5-page PDF built from Stack Overflow’s database pages. Sounds crazy, but having these pages in hand is absolutely critical to understanding these concepts.
How to Think Like the Engine in Videos
Both the videos and the written articles cover the same basic topic – it just comes down to how you prefer to learn. If you prefer videos, here you go:
- The Clustered Index (25 minutes)
- Adding Non-Clustered Indexes (13 min)
- Key Lookups and Cardinality Estimation (21 min)
How to Think Like the Engine: Written Articles
- How to Think Like the SQL Server Engine: Introduction
- Why Query Plans Can Look Different on Different Servers
- Adding an ORDER BY
- The Perils of SELECT *
- What Happens When You Run a Query Repeatedly
- Adding a Nonclustered Index
- So Index Seeks are Great, Right?
- What’s a Key Lookup?
- The Tipping Point: When a Scan Makes More Sense
- Using Statistics to Build Query Plans
- When Statistics Don’t Help
- Building Wider Indexes to Deal with Bad T-SQL
- Should Columns Go in the Index Key or Includes?
- Included Columns Aren’t Free
- Index Key Column Order Matters a Lot
- When a Seek Isn’t: Scan/Residual Predicates
Next Steps
When you’re ready to learn more, check out my one-day online course, Fundamentals of Index Tuning. You’ll learn:
- How to design indexes for a query without looking at its execution plan
- How to pick the order of keys in an index
- How query parameter values can change which index is better
- Where to find index recommendations using query plans, DMVs, and sp_BlitzIndex
- How SQL Server’s index recommendations are built, and why they’re often wrong