Join Joey D'Antoni for an in-depth discussion in this video Temp tables, part of SQL Server Performance for Developers.
- [Instructor] So lots of times, we need to pass data between various objects, sessions, or types of code, whether it's passing data from one procedure to another, or to a function, or even holding data for comparison, or even joining data between other tables. In SQL Server, the most common way we do this is through the use of temp tables. If you've ever been on a job interview with me, you'll know that one of my favorite job interview questions for DBAs, or even SQL developers is, what does tempdb do in SQL Server? And it's a very open-ended question because tempdb does a whole lot of things, but its primary goal and focus is to store and retain temporary tables.
One important thing to note is that tempdb is recreated every time SQL Server restarts, so temp tables are truly not persistent. They're tied to your session as well, so when your session goes away, the table will go away, and that being the session that created the temporary table. So let's talk a little bit more about temp tables. So what are temp tables? These are just workspaces, and temp tables can be used for a wide variety of things, whether it's passing data between data structures, that's probably the most common scenario I've used them for, or building a comparison list in lieu of using a cursor or some sort of other object.
I've used them to generate code. I've used temp tables just when I want to have throwaway data. More importantly, SQL Server uses them for a wide variety of things. SQL Server uses tempdb as worktables for storing data. When you have a spill from a query comparison, it spills that data into temporary worktables in tempdb. If you're using READ_COMMITTED_SNAPSHOT isolation level, which is an isolation level that allows users to read data while other users are writing data, tempdb is going to hold the version history of that data.
If you're using an always-on availability group, and you're on a readable secondary, SQL Server is going to create stats and store them in tempdb when you query off that readable secondary. So there's lots and lots of things that SQL Server uses this construct for. That means it's very important that you have good performance from your tempdb. Tempdb mostly lives in memory, but it's important to have it on a fast disc, so that if you do have to access disc, your performance is good. There are two types of temporary tables that you'll use: local and global, and these have a specific designation.
Local tables are designated with one pound sign, which you'll see in the demo, and global temporary tables are designated with two pound signs. If you create a table that doesn't have a pound sign, or two pound signs, in front of it within tempdb, it will not be dropped when your session is dropped. Unless you explicitly drop it, it will remain in tempdb until the SQL Server instance is restarted, or it's explicitly dropped. Temdb space can be valuable, so you don't want to do that, 'cause you don't want to run out of space for tempdb.
So couple of different things about temp tables. You can include indexes on temporary tables. You can also compress temporary tables. So that's a scenario where you might have to store a large amount of data for a short period of time, and you want to reduce the amount of space it takes up, in either memory or on disc. You can go ahead and compress that, especially if you have additional CPU. One thing to do when you're creating temp tables in your application design is only include the columns and rows that you need for whatever data interchange you have going on. You don't want to add more data than you need, and blow out the size of tempdb.
This can be a common problem when tempdb runs out of space, and that's just like any other database running in a space. It grows beyond the amount of space it has available in the file system, and it can't grow anymore, and queries start failing. This is a fairly common scenario when you have runaway tempdb usage by one or more particularly large queries, and it's a common scenario that gets asked when this happens, from developers and DBAs: how do I shrink tempdb to get it back down to size? Well, once it's blown out to that size, it's very difficult to shrink it, so you typically would have to restart the instance to get it back down to size.
You want to pay attention to your queries, and make sure nothing is consuming a ton of tempdb space, and blowing it out, and after you've worked with the SQL Server instance for a period of time, you'll get a good understanding of what normal tempdb utilization looks like, and you'll notice if something is running away. Also, you can monitor for that. One other important thing to note is that you should create multiple tempdb data files, and the right number for this is typically the number of cores on your server, up to eight. This is to reduce contention in memory for those data file pages.
A lot of people think this has to do with disc, and by spreading the files over multiple discs you'll get better I/O. Really, the pages that we're worrying about having contention on almost always live in memory, so it's more that we'd have memory contention if we only had a single page, that only a single process could write to that page at a given time. So that's important to note. It is done for you in SQL Server 2016 and above. One warning: if you're using SQL Server on an Azure VM template that came with SQL Server already installed, those templates were built with a machine that had one CPU, so your tempdb will only have one file.
One thing we're going to demo in another video, for particularly busy workloads, you can use in-memory OLTP, quote-unquote, temp tables, and these aren't truly temp tables. These tables are in memory in your local database, not in tempdb, but it simulates the behavior of tempdb, and allows you to get better throughput, and possibly even offload some load on tempdb. Let's take a quick look, just to see how these tables work in reality. So here we're going to say USE TempDB. We don't have to say USE TempDB, because we're specifying our table names with that pound sign, like we mentioned.
So here, we're creating a temp table called #temp. I wasn't very creative in my naming structure, and we're going to insert a single row into it, and then we're going to create a table called ##temp, which is going to be a global temp table, and we're going to insert a value into it, and let's go ahead and select star from each of these, and we're going to be able to see the data that we just inserted.
Let me make sure I'm in the right database first. Now we're going to select from sys.tables, which is a system table that's stored within our local database, so we do need to be in tempdb for this, and I'm saying WHERE name LIKE #temp, so this should return both of those tables for me, and it does. You can see one of them has a negative object ID. That happens with temp tables. The global name has a regular object ID, but the session-bound temp table has a negative one.
So let's go look at a second session, and see if we can select from that table that I created in session 53. You'll notice that we're in a second session now. You can identify this easily just by the fact that it's another tab in Management Studio, but if you want a little more info, you can see the session ID in the tab. It's shown there, and here, mine is 81. Yours is going to be different. It just depends on where you are on your system allocation, and how busy your system is, but this is session 81. My other session is session 53. So here, if I go to select from the local temp table that I created in the other session, you'll note I'm not going to be able to read it.
I get an Invalid object name #Temp, even though I'm in tempdb, and I know that table exists. If I try to create a table called #Temp, it will allow me to do that. Both of these are going to be bound to my session. Now what I'm going to do is try to select from the global temp table that we created in the other session. I can see that table was created, and I can select from it, and I get the data that's stored there. It's global, it can be used by other sessions. Finally now, I'm going to redo my query from sys.tables in tempdb to show all three of these temp tables that we created.
So you'll note we have two temp tables, and there's a difference in casing on these because of how I happened to type it. Two and three, they both have negative object IDs. They are both bound to their sessions. So let's see what happens if we kill session 53. So we're going to go ahead and close the session, and then rerun this query, and now we only have one table. So those tables were removed as the session ended.
- Query execution
- How to read an execution plan
- What not to do with SQL Server
- Why cursors are bad
- Why scalar UDFs are expensive
- Datatypes and design
- What is a columnstore index?
- Transaction isolation