Paul White Explains Temp Table Caching 3 Ways

Paul White (Blog, @SQL_Kiwi) is a legendary SQL Server blogger who’s written a ton of good material. You should read all of it. You won’t, and I can say that with authority because…I don’t read it all either. It’s too much.

But when your job eventually requires you to understand temp tables way better than you do today, read these 3 posts in this order – which is probably different than what Paul would recommend, but I think they flow better this way:

1. Temporary Table Caching Explained – when you create a temp table, you probably think it’s exclusive to your own session. It’s not. Its structure (but not its contents) can be reused by other sessions, and understanding that this happens is the first step to really understanding the complexity in tuning query problems with temp tables. Paul has a few notes in here that are outdated (like around creating indexes on table variables), but overall, the concepts are still solid.

2. Temporary Table Caching in Stored Procedures – after the above, now let’s see what happens when you repeatedly call a stored procedure that uses temp tables. To do it, he starts by showing you how a real table would work, then switches over to temp tables. He shows why good stats help temp table plans, how bad cached ones hurt, how to fix it with updating stats, and how table variables perform differently.

3. Temporary Object Caching – rehashes a little of the earlier posts, but then also layers in TempDB performance issues and behavior of newer versions of SQL Server.

For years, I’ve been pointing my clients at these 3 posts whenever they hit TempDB problems – and these problems kept coming up so often that I ended up building my new Fundamentals of TempDB class. However, if you don’t have time to wait for that, or if you’d just like to get started now, Paul’s posts are one heck of an on-ramp. Enjoy.