Press "Enter" to skip to content

Category: Temp Tables

Temp Tables as a Release Valve for Design Issues

Published 2020-12-03 by Kevin Feasel

Erik Darling shows a benefit of temp tables and cold, bitter experience:

A lot of the time when I see queries that are written with all sorts of gymnastics in the join or where clause and I ask some questions about it, people usually start complaining about the design of the table.

That’s fine, but when I ask about changing the design, everyone gets quiet. Normalizing tables, especially for Applications Of A Certain Age™ can be a tremendously painful project. This is why it’s worth it to get things right the first time. Simple!

Rather than make someone re-design their schema in front of me, often times a temp table is a good workaround.

Click through for an example.

Comments closed

Parallel Inserts into Temp Tables

Published 2020-12-02 by Kevin Feasel

Erik Darling explains the pre-conditions for parallel insertion into temporary tables:

If you have a workload that uses #temp tables to stage intermediate results, and you probably do because you’re smart, it might be worth taking advantage of being able to insert into the #temp table in parallel.

Remember that you can’t insert into @table variables in parallel, unless you’re extra sneaky. Don’t start.

If your code is already using the SELECT ... INTO #some_table pattern, you’re probably already getting parallel inserts. But if you’re following the INSERT ... SELECT ... pattern, you’re probably not, and, well, that could be holding you back.

There are enough pre-conditions that this becomes a decision rather than an automatic. Especially if you’re dealing with temp tables with indexes and want to take advantage of temp table reuse, which I believe precludes changing the structure of the table (including adding indexes) after creation.

Comments closed

Dropping Temporary Tables at the Beginning of a Procedure

Published 2020-07-09 by Kevin Feasel

Richard Swinbank walks through a technique to drop temp tables as a procedure begins:

Local temporary tables – tables with names that begin with a single # character – are dropped automatically by SQL Server when they are no longer in scope. So why drop them explicitly at all?

I’m writing this from the perspective of a data engineer. ETL processes or their components are often implemented as T-SQL stored procedures (SPs) which might move significant amounts of data around and take a while to run. I’m not talking about the sort of SP that needs to run in 30ms to guarantee application responsiveness!

Sometimes it’s useful to store intermediate results in temporary tables, sometimes it’s necessary, and sometimes you find yourself maintaining code built by someone who just really loves them. Adding explicit DROPs to code like this can make development and maintenance a bit smoother.

My concern with doing this is that it prevents temporary object caching. Granted, Richard explicitly calls out that these are long-running procedures (and probably one of a particular procedure running at a time), so that does reduce the concern accordingly.

Comments closed

Who and What is Using Your tempdb Space?

Published 2020-05-14 by Kevin Feasel

Guy Glantser helps us troubleshoot who is using tempdb space and for what purpose:

In the past two weeks I encountered 3 different scenarios in which tempdb has grown very large, so I decided to write about troubleshooting such scenarios.

Before I describe the methods for troubleshooting tempdb space usage, let’s begin with an overview of the types of objects that consume space in tempdb.

The answer, of course, was Professor Plum in the Aviary with the eager spool.

Comments closed

Columnstore and Memory-Optimized tempdb

Published 2020-05-08 by Kevin Feasel

Erik Darling has a bucket of cold water for us:

In SQL Server 2019:

– Exciting stuff: In memory tempdb!
– Exciting stuff: sp_estimate_data_compression_savings can evaluate columnstore compression!
– Disappointing stuff: If you use in memory tempdb, you can’t have any columnstore anything in tempdb

That means if you’re using sneaky tricks like clustered columnstore indexes on temp tables to induce batch mode, you’re gonna get a lot of errors.

Likewise, you won’t be able to evaluate if columnstore will help your tables.

Click through to understand the extent of this limitation. Hopefully this is something we see addressed in vNext and a CU for 2019.

Comments closed

Queries Using tempdb: a Whodunit

Published 2020-05-05 by Kevin Feasel

Dave Bland shares a database detective story with us:

To find the culprit, we tracked down an old informant named sp_who2.  At first he had no comment, but we knew he had some information that could help us, so we kept after him.  Finally, he grew tired of us and gave us something just to make us go away. He said we needed to talk to his associate sysproccesses.  At first this guy was hard to find, but we located him sitting at a table.  He wasn’t really doing anything, just sitting there. So we approached him and sat down to see if we can get what we are looking for.  He also had no comment and referred us to his lawyer, sys.dm_exec_sessions.  After some searching, we were able to catch up with him at the DMV and he was not in a good mood.  He gave up some information, just not exactly what we are looking for.

Dave shows how we can figure out who created a specific temp table (a global temp table, in this case), the query that account used to create the temp table, and the time the temp table was created.

Comments closed

The Problems with Shrinking tempdb Data Files

Published 2020-04-29 by Kevin Feasel

Andy Mallon explains why you should never shrink tempdb data files:

I recently wrote about growing, shrinking, and removing tempdb files. In that article I explained that SQL Server won’t move a page that contains an internal worktable object, and thus trying to shrink tempdb files can be futile. Today, I’m going to explain how attempting to shrink tempdb files can actually be harmful.

Andy has good advice for tempdb here. Shrinking other database is generally bad but sometimes necessary; shrinking tempdb can lead to all kinds of problems.

Comments closed

Fun with tempdb

Published 2020-04-22 by Kevin Feasel

Andy Mallon walks us through setting up tempdb:

There are three problems I’ve got to fix. I need to (1) remove those two extra files, (2) grow the tempdb log file, and (3) even out the size of the data files (and shrink them a little to make room for the larger log file. We’re going to tackle these in the reverse order than I listed them–partially out of necessity, and partially because it’s going to be easier.

Click through to see how Andy sets up tempdb. This is a good way to set up tempdb.

Comments closed

Tempdb Issues You Might Have

Published 2020-04-22 by Kevin Feasel

Fabiano Amorim walks through a few of the tricky issues around tempdb:

One of many performance improvements that came with SQL Server 2014 is that it doesn’t flush dirty pages created in a minimally logged operation on tempdb. This gives you the benefit of having faster (compared to prior versions) inserts, but it caused another problem as those allocated pages may take a lot of time to be removed from the buffer pool data cache. Before discussing the problem, quickly look at the benefit and then understand some important concepts of flush dirty pages on tempdb.

Click through to learn more about the fix, and then a fix to the fix…which opened up a new avenue to fix. This kind of thing is why operating at scale is so difficult: the solution to one problem often becomes the avenue to a new problem.

Comments closed

Combining User-Defined Types and Temp Tables

Published 2020-04-03 by Kevin Feasel

Andy Levy tries to make cats and dogs live together:


This tripped me up a few weeks ago, but once I stopped and thought about for a moment it made total sense. I was trying to copy some data into a temp table and got an error I’d never encountered before.

Column, parameter, or variable #1: Cannot find data type MyStringType.

What’s that all about? Let’s find out.

I don’t think it spoils things to say that Andy’s story is a tragedy and not a comedy. But in fairness, the number of shops using user-defined types (as opposed to user-defined table types) is probably not enormous.

Comments closed