T-SQL Tuesday #146 – Upending Preconceived Notions

Happy 2022! Let’s start the year off right by joining this month’s T-SQL Tuesday blog party.

Andy Yun (t | b) is this month’s host. Our challenge this month is:

 …to think about something you’ve learned, that subsequently changed your opinion/viewpoint/etc. on something.

There’s a whole lot of things I could probably talk about, but I think we’ll stick to SQL Server for now.

I was always taught to clean up after myself so I thought dropping my local temp tables after creating them in a proc did that. I knew that once the session was dropped, the table no longer was there so SQL Server would eventually take care of it. But then you had the cases where you ran the same stored proc multiple times in the same session so you wanted to make sure the table was gone at the end of your procedure to prevent issues.

Pam Lahoud (t | b) has a great session called: TempDB, The Good, The Bad, The Ugly. She presented it at the EightKB conference but I happened to catch it at a NESQL meetup. I highly recommend that you watch it; it’s linked in the title. TempDB is one of my weaker areas and I learned so much by watching this.

My “a-ha!” moment was when she mentioned that you don’t need to drop temp tables that you created at the end of the stored procedure.

Wait, wha???

Yes! In SQL Server 2005, Microsoft added temp table caching as a way to solve metadata contention in tempdb. When you “drop” the table as part of a stored procedure, it doesn’t really get dropped at all. There is a copy of the table’s metadata – and its statistics – that gets left in the cache. So when you call the stored procedure again, the statistics are available for the procedure so SQL Server can use that to help create the execution plan.

There were two things about this that kind of blew my mind. First, the statistics of that temp table are reusable. If you set up a temp table and if it’s similar in scope each time you call the stored proc, you already have an execution plan cached that can be reused. Second, I’ve been doing this wrong since SQL Server 2005. That’s a really long time to not know that you don’t have a to drop the temp table at the end of the proc. You still have to make sure your stored procedure can work properly if you run it multiple times in the same session.

I know my overly simplified explanation doesn’t go into all of the details. Luckily, there are other people who can explain it much better than I can. A couple of resources if you’re interested in learning more:

• Watch Pam’s session. Here’s the link to her EightKB Confererence session again.
• Paul White has a couple of great blog posts about this. You can read them here and here.
• Franklin Yamamoto at SQL Grease also has a nice explanation here.

There is a lot that goes into this and a lot of different caveats. It’s worthwhile to read up on these.

I can’t wait to read everyone else’s posts and unlearn something that I had thought was good practice.

Thanks for hosting, Andy!