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.

You may change the query that is slow as well as anything else in the procedure that will help you make that statement faster.

In the solution videos, I’ll step through multiple strategies to figure out which statement in the procedure is slowing it down the most - because in real life, you need to have a whole bag of tricks in different situations.

I’ll also show sample solutions that speed up the stored procedure, along with code patterns that make tuning procedures simpler.

The challenge script in the first lesson creates a database on your test instance - no need to restore anything! Go ahead and get started.

Demo scripts

Grab the scripts here https://github.com/LitKnd/SQLWorkbooks/tree/main/tuning_a_stored_procedure

Lessons

Your Problem Stored Procedure (14 minutes)

Solution Scripts, Trace Flag 7412, and Live Query Plans (14 minutes)

Finding the slow query with the plan cache (6 minutes)

Finding the slow query with Query Store (8 minutes)

Tuning the slow query (15 minutes)

Integrating the changes, and other improvements to the procedure (13 minutes)