How to Tune SQL Server Indexes with Execution Plans
Thursday, March 9, 2017
Pre-Conference, Lisbon, Portugal
Full Day Session (in-person event) – 130€ Early Bird / 180€ full price.
SQL Server asks for a lot of indexes — but it doesn’t always ask for the right index! You need to know the essential principles of index design to create the best nonclustered index, filtered index, or indexed view to speed up your critical queries.
In this example-packed day long session, you will work through a series of simple problems and solutions to learn how to create and tune disk-based, rowstore indexes. You will learn how to choose the right key columns, when included columns help, when to use special indexes, and how to avoid workload-killing gotchas.
Throughout the day, you’ll learn to use query execution plans to decode how SQL Server is using your index, and whether or not changing the index is likely to make your query even faster. You will see how SQL Server 2016’s Query Store feature offers you new tools for improving your indexes, and how Query Store compares to index Dynamic Management Views. You’ll leave with all the scripts for the problems and solutions covered in the course.
Students will learn to:
- Interpret execution plans to understand how indexes are being used
- Use execution plan analysis to suggest index changes to improve query performance
- Measure and record query performance accurately to document index efficiency and improvement
- Critique and improve SQL Server’s suggestions for indexes
- Practice designing indexes, predicting how SQL Server will use them, and test the results
Contents/Topics covered in the workshop:
- Nonclustered Index Key Choice
- Keys vs Included Columns
- Indexing for TOP / ORDER BY
- Indexing a non-SARGABLE Predicate
- Indexing for GROUP BY
- Changing a Clustered Index
- Strategizing Index Optimization