Fun With Temp Tables

Kenneth Fisher answers a Brent Ozar pop quiz regarding temp tables:

Go ahead and give it a shot .. I’ll wait.

So? What do you think? Did you get it right? I did, but I wasn’t 100% certain, nor did I initially think through all of the implications. The question actually has more depth to it than you might think on the surface. So I thought it would be fun to go through what I was thinking before I made my decision, what actually happens, and what I realized afterward.

Read on for the answers.  For bonus fun, check out Brent’s comment to the post.

Cardinality Estimation On Memory-Optimzied Table Variables

Jack Li explains that the cardinality estimator works the same for memory-optimized table variables as it does for regular table variables:

In a previous blog, I talked about memory optimized table consumes memory until end of the batch.   In this blog, I want to make you aware of cardinality estimate of memory optimized table as we have had customers who called in for clarifications.  By default memory optimized table variable behaves the same way as disk based table variable. It will have 1 row as an estimate.   In disk based table variable, you can control estimate by using option (recompile) at statement level (see this blog) or use trace flag 2453.

You can control the same behavior using the two approaches on memory optimized table variable if you use it in an ad hoc query or inside a regular TSQL stored procedure.  The behavior will be the same. This little repro will show the estimate is correct with option (recompile).

Jack also explains how this works for natively compiled stored procedures (spoilers:  it doesn’t), so read the whole thing.

Temp Table Caching

Paul White explains how to cache temporary objects:

Table variables and local temporary tables are both capable of being cached. To qualify for caching, a local temporary table or table variable must be created in a module:

  • Stored procedure (including a temporary stored procedure)
  • Trigger
  • Multi-statement table-valued function
  • Scalar user-defined function

The return value of a multi-statement table-valued function is a table variable, which may itself be cached. Table-valued parameters (which are also table variables) can be cached when the parameter is sent from a client application, for example in .NET code using SqlDbType.Structured. When the statement is parameterized, table-valued parameter structures can only be cached on SQL Server 2012 or later.

The first time I heard about this was a SQL Saturday presentation that Eddie Wuerch did.  Paul does a great job talking about the requirements (and noting that table variables are eligible as well), making this well worth the time to read.

Join Simplification With Table Variables

Erik Darling has an example of how adding a key constraint to a table variable allowed the optimizer to filter it out:

I was trying to come up with a demo for something totally different. Don’t ask. Seriously. It’s top secret.

Okay, so it’s just embarrassing.

Anyway. I had these two queries. Which are actually the same query twice. The only difference is the table variable definition.

Click through for the demo and additional information.

Table Variables Use TempDB Too

Derik Hammer proves that classic, non-memory-optimized table variables use disk:

Table variables use tempdb similar to how temporary tables use tempdb. Table variables are not in-memory constructs but can become them if you use memory optimized user defined table types. Often I find temporary tables to be a much better choice than table variables. The main reason for this is because table variables do not have statistics and, depending upon SQL Server version and settings, the row estimates work out to be 1 row or 100 rows. In both cases these are guesses and become detrimental pieces of misinformation in your query optimization process.

It’s worth the read.

Table Variable Spills

Erik Darling looks at queries using table variables spilling to disk:

Boy oh boy. Boy howdy. Look at all those physical writes. We spilled everything to disk. That’s right at the 2.4 GB mark, which is the same size as the Votes table. We should probably know about that, right?

Read on for a comparison against temp tables.

“Permanent” Temp Tables

Brent Ozar shows two ways of creating “permanent” temp tables:

The first one disappears when my session is over, but the latter two persist until the SQL Server is restarted.

Why would you ever do the latter two? Say you need to share data between sessions, or between different applications, or staging tables for a data warehouse, or just faster tables that live on local SSDs in a cluster (as opposed to slower shared storage), or you wanna build a really crappy caching tier.

Brent also talks about stored procedures.

Temp Table Usage

Paul Randal discusses common temp table anti-patterns:

It’s quite common for there to be a latching bottleneck in tempdb that can be traced back to temporary table usage. If there are lots of concurrent connections running code that creates and drops temporary tables, access to the database’s allocation bitmaps in memory can become a significant bottleneck.

This is because only one thread at a time can be changing an allocation bitmap to mark pages (from the temp table) as allocated or deallocated, and so all the other threads have to wait, decreasing the workload throughput. Even though there has been a temporary table cache since SQL Server 2005, it’s not very large, and there are restrictions on when the temporary table can be cached (e.g. only when it’s less than 8MB in size).

This is great advice; read the whole post.

External Temp Tables And Plan Cache Bloat

Sebastian Meine warns us about using external temporary tables in stored procedures:

When a stored procedure is compiled that is accessing an external temp table, SQL Server has no guarantee that the next time this stored procedure is called it is called from the same connection. However, if it is called from a different connection, the accessed temp table might contain significantly more (or less) data making a different execution plan preferable.

A simple way to deal with this situation is to force a recompilation every time a procedure that works with external temporary tables is executed. SQL Server is not going that route. Instead, SQL Server caches the procedure once for each connection. That can safe a significant amount of CPU resources when the procedure in question is called within a loop.

Try to avoid using external temp tables.  There are some cases in which it’s a very useful construct, but


July 2017
« Jun