So what happened to cause this inconsistent growth and how should I fix it?
Tempdb is used (and can grow) when users explicitly create objects like temp tables, variables, cursors, or stored procedures. Tempdb is also used when the database engine creates work tables for sorts or spooling, and row versioning operations.
It’s often difficult to diagnose what happened on a SQL Server after the fact. There are many resources online to determine what is currently using tempdb but not a lot of information from processes that may have run days ago.
Read on for some tips to help manage tempdb growth better.
SQL Azure has added something called database scoped global temporary tables.
Azure SQL Database supports global temporary tables that are also stored in tempdb and scoped to the database level. This means that global temporary tables are shared for all users’ sessions within the same Azure SQL database. User sessions from other Azure SQL databases cannot access global temporary tables.
They way you add these is by using a double pound sign
Click through for more. I will occasionally use a global temp table, mostly in conjunction with Central Management Server queries going over a set of databases on each instance, but I limit my usage to manual operations and nothing as part of a normal application or administrative process.
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.
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.
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)
- 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.
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 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.
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.
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.
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.