Query Tuning

Tuning a Stored Procedure - SQLChallenge (1 hour 10 minutes)

Tuning a Stored Procedure - SQLChallenge (1 hour 10 minutes)

Your challenge is to identify which statement is slowing down our stored procedure the most, then tune the code to speed it up.

Speed Up the "Popular Names" Query SQLChallenge (46 minutes)

Speed Up the "Popular Names" Query SQLChallenge (46 minutes)

You’ve got a query that is just too slow!

Forcing Parallelism SQLChallenge (48 minutes)

Forcing Parallelism SQLChallenge (48 minutes)

A database administrator has changed Cost Threshold for Parallelism on the instance, and you’ve got a query that’s slowed down.

Why Table Partitioning Does Not Speed Up Query Performance - With One Exception (52 minutes)

Why Table Partitioning Does Not Speed Up Query Performance - With One Exception (52 minutes)

Learn why SQL Server’s table partitioning feature won’t make your queries against disk-based rowstore indexes faster– and may even make them slower.

Try the course quiz anytime.

Auto Tuning with Automatic Plan Correction in Query Store (1 hour 8 minutes)

Auto Tuning with Automatic Plan Correction in Query Store (1 hour 8 minutes)

WARNING: I do NOT currently recommend enabling the Automatic Plan Correction feature due to issues described in my post, Automatic Plan Correction Could be a Great Auto Tuning Feature for SQL Server. Here’s why it Isn’t.

Want a built-in assistant performance tuner in SQL Server?

Try the course quiz anytime.

The Case of the Slow Temp Table: A Performance Tuning Problem (50 minutes)

The Case of the Slow Temp Table: A Performance Tuning Problem (50 minutes)

Why would using a temp table cause a stored procedure to slow down dramatically and use massively more logical reads, compared to a permanent table?

Indexing for Windowing Functions (45 minutes)

Indexing for Windowing Functions (45 minutes)

Get the best performance for your windowing functions.

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

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.

Query Tuning with Hints & Optimizer Hotfixes (2 hours 15 minutes)

Query Tuning with Hints & Optimizer Hotfixes (2 hours 15 minutes)

In this course you’ll learn the pros and cons of using hints, trace flags, and scoped database configuration to tune your queries in SQL Server.

Try the course quizzes anytime: using hints, recompile, optimizer hotfixes

Problem Queries in Table Partitioning (1 hour 30 minutes)

Problem Queries in Table Partitioning (1 hour 30 minutes)

Some queries may get slower after you partition your tables: the SQL Server optimizer doesn’t always use indexes the same way after those indexes are partitioned into chunks.