Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.
on November 30, 2016
Want to learn to tune indexes in SQL Server? I’ll be teaching a day-long pre-conference session in Portugal in March. Hope to see you there, or at SQL Saturday Lisbon (free!) the following weekend.
Here’s the details on the course and how to reserve a seat.
How to Tune SQL Server Indexes with Execution Plans
Here’s an overview of what you’ll learn in the course, or scroll on down to read the details.
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