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?

In this course…

  1. Watch a demo of weird temp table performance problem in SQL Server
  2. See how to measure the problem
  3. Try your hand at figuring out ways to speed up the temp table
  4. Then watch me explore the issue and see a couple of possible workarounds to make that temp table faster.

The problem shown in this course applies to SQL Server 2005+. The demo code has been written and tested against SQL Server 2016 and SQL Server 2017 CU1.

Course scripts

https://github.com/LitKnd/SQLWorkbooks/tree/main/case_of_the_slow_temp_table

Each video has closed captions in English. A written transcript is included with each lesson.

Lessons

The case begins (2 minutes)

Meet your problem stored procedure (10 minutes)

Your perf tuning challenge (3 minutes)

Analyzing what we've learned so far (3 minutes)

A test: what if I update fewer rows? (6 minutes)

Comparing execution plans (7 minutes)

Procedures vs ad-hoc TSQL (11 minutes)

Speeding up the temp table (10 minutes)