There comes a time in everyone’s life that they need to be concerned about tempdb. I know… we all have configured it correctly and it’s running great. The application and all of the developers are using it like crazy …Wait… that may not be a good thing. Right?
Management might not see it as a good thing when they receive the bill every month. You do not want to come to a point where management has to determine if it is worth keeping you on board or paying for the additional tempdb overhead. I think we have heard that everything is cheap. It may be but it adds up really quickly. With many orgs moving to the cloud, I think we as IT professionals need to revaluate what we are doing and find better ways to do things. We should have the same concerns that we did a while back and avoid waste when possible.
The following solution can help you take some of the load off of tempdb. Yes, this is not a one size fits all but there could be some value to utilize it when you can. It discusses the use of #temp tables, @table variables, and CTEs and the use inside of tempdb.
With query below you can see that the logic is using a #temptable. There are different ways to very if TempDB is being utilized or not. I am going to use the following logic as I find it easy to use for all three scenerios:
SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%#%';
With #temp tables they exist in tempDB as long as the session is active or before the temp table is dropped. Because of this it is one of the easier ones to validate. Looking at the results below, you can see that the #tempDB is stored in the tempDB TABLE CATALOG. It is referenced as "temptable_______________...",
With this window you can see that we are using the exact same logic above except that we are replacing the #temptable with a table variable. Table Variables do not show up under tempdb.
INFORMATION_SCHEMA.TABLES the same way that temp tables do. I had to do a little more work to prove it to you. @Table Variables show up with “#” followed by a set of characters that do not match the actual name. So in order to validate it I had to use:
"SET STATISTICS IO ON;"
By using this I am able to see the disk activity that is associated to the query that I am running. Remember TempDb is on disk. You can see the Messages below in the first window. You can see that ‘#A7225307’ is being used with the other tables in the logic below.
Here’s the proof that Table Variables are used by TempDb. You can see that the table variable above (‘#A7225307’) is returned in the tempdb.
INFORMATION_SCHEMA.TABLES select statement below.
Many people think that because table variables are named as a variable that they are stored into memory but according to Microsoft the problem with this is that if there is more data than can be stored into memory that it needs somewhere to put it so that it is not lost. It puts the structure to disk and runs it as memory when it can but has it ready on disk when needed. This can be extremely value when you are working with large amounts of data at a time.
Our third option are CTEs or Common Table Expressions : With CTEs you are putting your object in a temp result set however it does not use tempDb like the two prior options. Looking at the example below you can see that the logic is very similar to the other two temp objects. In this case there are not any inserts into the temp object because CTE run very much like a subquery and is processed at the time of execution. It uses the same resources that any other objects use and the performance is relative to the query, environment, amount of records, etc. You can see below that this logic is not calling any reference to tempdb.
As anything else, there is not a one size fits all. You might find some value taking the load off of tempdb anyway that you can. This is one way that you can do it.