How to Think Like the SQL Server Engine – Brent Ozar Unlimited®
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 my totally free How to Think Like the Engine class, you’ll learn how the SQL Server engine looks at your indexes and builds your query results.
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 helps you understand these concepts. You can also download the slides.
Part 1
- SET STATISTICS IO ON shows # of 8KB pages read.
- WHERE without a supporting index = table scan
- ORDER BY without a supporting index = CPU work
- Sorting gets expensive if you have to read all columns.
- SQL Server caches data pages, not query output.
Part 2
- Indexes are literally a copy of parts of the table, take up more space.
- Don’t index hot columns (columns that change often).
- Less indexes are good.
- No indexes = SELECT scan tables.
- Too much indexes = more D/U/I writes.
Part 3
- Table scan is a fixed cost, index seek + key lookups is variable cost.
- Key lookups can be expensive if there is a lot of rows.
- SQL Server uses statistics to decide which execution plan to use.
- Use index has a statistic (1 KB Page).
- Key lookup means the query wanted more columns than we had in the nonclustered index.
Part 4
- You may have too many indexes if insert/deletes are slow, there are blocking, deadlocks.
- You might not have enough indexes if slow selects, you cache a lot of database in memory.
- First column in an index is very important, should be generally unique.
- WHERE/GROUP/JOIN/ORDER columns good candidates for the index keys.
- SELECT columns good candidates for INCLUDE.
- Less column index, less benefit due to key lookups.
- More column index, more benefits but also more problems.
Related Pages:
https://www.brentozar.com/archive/2017/10/estimated-subtree-cost-query-bucks-no-really/
https://www.brentozar.com/archive/2021/09/what-is-a-cost-based-optimizer/
Comments