We Don't Need Roads: A Look Into SQL Server Indexes

Have you ever ran a SQL against SQL Server and it was so slow that you wondered if the database was from 1955? You could use a Delorean or you can create indexes in your database. This session will introduce you to SQL Server indexes. Indexes are a powerful tool to improve the performance of our queries. We'll discuss how indexes are structured and why they speed up queries, how INSERT, UPDATE, and DELETE queries are affected by indexes and how to identify if an index is necessary. Then, we'll take a look at an Entity Framework application and see how EF reacts to our fresh new indexes. If you want to make bad query performance a thing of the past this session is for you.

Transcript

1. WE DON'T NEED ROADS: A LOOK INTO SQL SERVER INDEXES RICHIE RUMP JORRISS LLC @JORRISS HTTP://JORRISS.NET
2. WHO IS THIS GUY?
3. WHAT WE’RE GOING TO TALK ABOUT • Why indexes? • How indexes are used • DB storage basics • Index structure • Clustered indexes vs non-clustered • Includes, fill factor and covering
4. A TRAGEDY
5. Great Scott!
7. WHY ARE INDEXES IMPORTANT? “An index makes the query fast.” Reference: http://use-the-index-luke.com/sql/anatomy
8. WHY ARE INDEXES IMPORTANT? “An index makes your query fast.” Reference: http://use-the-index-luke.com/sql/anatomy
9. HOW ARE INDEXES USED BY SQL SERVER? SQL Server creates a plan to determine what’s the best way to execute the query. (Unlike some developers)
10. EXECUTION PLANS Demo
11. TYPES OF INDEXES • Clustered • Nonclustered • Heaps • Column Store • XML • Spatial • Full-Text • Clustered • Nonclustered • Heaps • Column Store • XML • Spatial • Full-Text
12. HOW DO INDEXES WORK? In order to understand the magic of indexes we need to understand how SQL Server stores data.
13. HOW SQL SERVER STORES INFORMATION Pages • Page Size: 8K • Used to store just about everything. • Page Header: Metadata about the page. (Page Number, Type, etc.) • Offset Array: Pointers to where each row begins. Page Header Records Offset Array
14. HOW SQL SERVER STORES INFORMATION Page Header Row 1 1 Page Header Row 1 1 Row 2 2 Page Header Row 1 1 3 Row 2 Row 3 2
15. WHAT HAPPENS WHEN A PAGE IS FULL? Page Header Row 1 1 Row 2 Row 3 Row 4 Row 5 2 3 4 5 Page Header Row 6 6 Row 7 Row 8 Row 9 Row 10 7 8 9 10 Page Header Row 11 11 Row 12 Row 13 Row 14 Row 15 12 13 14 15
16. HOW INDEXES ARE STORED • B-Tree or Balanced Tree • The tree is made up of Pages
17. Leaf Level Intermediate Level Root Level HOW ARE INDEXES STORED 1 - 400 201 - 400 1 - 200 151 - 200 101 - 150 251 - 300 201- 250 101 - 200 201 - 300 301 - 400 1 - 100 301 - 350 51 - 100 1 - 50 351 - 400
18. HOW ARE INDEXES STORED 1 - 400 201 - 400 1 - 200 151 - 200 101 - 150 251 - 300 201- 250 101 - 200 201 - 300 301 - 400 1 - 100 301 - 350 51 - 100 1 - 50 351 - 400 SELECT UserId, Title FROM Posts WHERE UserId = 160 CREATE NONCLUSTERED INDEX IX_Posts_OwnerUserId ON Posts (OwnerUserId)
19. CLUSTERED INDEXES • What is a table, Alex? • Only one per table! • Can the key be more than one column. • Must be unique (SQL Server will help if it isn’t.) • Can’t be more than 900 bytes combined
20. Leaf Level – THIS IS SPARTA THE DATA! Intermediate Level Root Level CLUSTERED INDEXES 1 - 400 201 - 400 1 - 200 151 - 200 101 - 150 251 - 300 201- 250 101 - 200 201 - 300 301 - 400 1 - 100 301 - 350 51 - 100 1 - 50 351 - 400
21. NONCLUSTERED INDEXES • Subset of data optimized for searching. • Key can be more than one column • Can’t be more than 900 bytes combined • Doesn’t have to be unique. • Max of 999 per table. (If I find 999 indexes on your table I will find you…)
22. Leaf Level – Key Data and Included columns. Intermediate Level Root Level NONCLUSTERED INDEXES 1 - 400 201 - 400 1 - 200 151 - 200 101 - 150 251 - 300 201- 250 101 - 200 201 - 300 301 - 400 1 - 100 301 - 350 51 - 100 1 - 50 351 - 400
23. CLUSTERED AND NONCLUSTERED INDEXES Demo
24. DEMO REVIEW • Key lookups are slow. Use Includes to combat. • Use columns in the index key if you intend on searching them. • Index order matters! • Suggested indexes may not be the best way to go.
25. SORRY, YOUR PAGE IS IN ANOTHER CASTLE Page Header Page Header Id: 2 1 Id: 3 Id: 7 Id: 12 Id: 14 2 3 4 5 Id: 6 1 2 3
26. FILL FACTOR • Minimizes page splits by leaving empty space in the page. • Is a number 0 to 100. • A value of 80 means that 80% of the page will be filled with data. • 0 and 100 both mean that 100% of the page will be filled with data. • Only applies to leaf pages
27. DISADVANTAGES OF INDEXES • Increases storage. • Creating duplicate copies of data. • Insert, Updates and Deletes are slower. • The DB has to delete data from the table and all of the related indexes. • Additional maintenance • Cannot create index on NVARCHAR(max)
28. BONUS! ENTITY FRAMEWORK! Demo
29. REFERENCES
30. THANK YOU Richie Rump @Jorriss http://jorriss.net http://dotnetmiami.com http://statisticsparser.com