After my previous article on “Inside Temp table object creation,” I received numerous inquiries about this topic. It seems that there is a need for a fresh perspective on these fundamental concepts that we have known for ages. One particular email caught my attention, and I believe it is worth addressing here on the blog.
The email reads:
“Hi Pinal, I really liked your article on Inside TempDB Table creation. The steps were very useful for me to understand how tempdb works. As a fresher in my first year of career, I somehow found the courage to send you this email. I am not sure if you will reply, but I thought you might have some pointers to help me. When I followed the steps on my local laptop, I could see the exact same results as you showed in your article. I was so excited that I decided to check what our dev/test environments look like, so I ran the DMV for user tables there. To my surprise, I found a number of objects with names like #B0D42C6C, #D5F46A7C, and so on. They are not like the table names you mentioned in your previous article. Even after running the same DMV after 5-10 minutes, I still see a lot of these tables on my system. What are these tables? Are they generated by the system? Are they used by my application? How do I get to know more about them?”
I believe this email brings a different dimension to the topic, and I appreciate the time taken by each and every person who reaches out to me. There are no simple questions in this world, and this is yet another example of how complex SQL can be and how we can learn from SQL Server every single day.
Now, let me explain what these temp objects are that my friend is referring to. These are also temporary tables created by users, but inside a stored procedure. To illustrate this, let’s take a look at the following code:
USE AdventureWorks2012
GO
DROP PROCEDURE TempTable_Objects
GO
CREATE PROCEDURE TempTable_Objects
AS
CREATE TABLE #temptable_in_sp (
id INT NOT NULL IDENTITY (1, 1),
name CHAR(30) NOT NULL,
DOJ DATETIME NOT NULL
)
-- Do whatever you want with the temp table :)
-- Wait, so that the SP doesn't complete fast :)
WAITFOR DELAY '00:00:05'
GO
Once the stored procedure is created, let’s open two sessions. In one session, we will execute the stored procedure, and in the other session, we will track the objects created inside TempDB.
Session 1:
-- Execute the SP in Session 1
EXEC TempTable_Objects
WAITFOR DELAY '00:00:05'
EXEC TempTable_Objects
Session 2:
-- Results when SP started
SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U'
WAITFOR DELAY '00:00:05'
-- Results when the first SP call ended
SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U'
WAITFOR DELAY '00:00:05'
-- Results when the SP was called again
SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U'
The output will show that the object names are similar to the ones mentioned in the previous article. However, unlike standard temporary table creation, SQL Server caches and keeps these objects so that subsequent users can reuse them. In this case, since my friend mentioned seeing a number of such values in TempDB, it means that there are many temporary tables created via the stored procedure that persist even after the session ends. The temporary table object ID confirms that they are being reused.
Upon further investigation, I discovered that there were reporting queries generating temporary tables every 15-20 minutes in a scheduled manner. This behavior was consistent in their environment. If you check SQL Server Management Studio Object Explorer, you will also see these objects listed there.
As I mentioned before, these experiences are great learning opportunities for me as well. I hope you have learned something new today through this exploration.