Why Creating an Index Can Slow Down a Query (1 hour 30 minutes)

Sometimes performance can go badly wrong if SQL Server has an imperfect index for an query.

Try the course quiz anytime.

In this session, I show you a query whose performance regresses after you add a nonclustered index. Your challenge is to figure out why the query got slower.

Try your hand at speeding up the query by tuning the TSQL. Afterward, I explore the query execution plan with you and walk you through multiple potential solutions, showing the pros and cons of each one.

Course scripts and sample execution plans are here: https://github.com/LitKnd/SQLWorkbooks/tree/main/why_creating_an_index_can_slow_down_a_query

This course features closed captions in English for each video, plus a written transcript in each lesson.

Lessons

Meet your problem query (20 minutes)

Why is this a problem? (7 minutes)

Explore the slow query execution plan (15 minutes)

Why did the query slow down? (5 minutes)

TSQL rewrites that do NOT fix the problem (5 minutes)

Solutions with index hints and local variables (13 minutes)

Using a temp table as a fake variable (8 minutes)

Solutions using Dynamic SQL (14 minutes)

Which solution is best? (3 minutes)

Quiz on indexes and query performance tuning