After my previous article on “Inside Temp table object creation”, I had a number of people ask me more about this topic. There seems to be a need for understanding these basic fundamentals that we have known for ages in a refreshingly new way. Though there were tons of mails that had come to me, there was one mail that caught my attention and I was pleasantly surprised to see the same. I thought it was worth the time to explain the same here over the blogs again. The Email reads like:
“Hi Pinal,
I really liked your article on Inside TempDB Table creation article. And the steps were very useful for me to understand how tempdb works. As a fresher and into my first year of career, I somehow found courage to send you this email. I am not sure if you will reply, still thought you will have some pointers to help me. When I did the step on my local laptop, I could see the exact steps shown by you. I was so excited and I thought let me know what our dev / test environments look like and I ran the DMV for user tables there.
I do get to see a number of objects with names like #B0D42C6C, #D5F46A7C and so on. They are not like the table names that you mention inside your previous article. And even after I run the same DMV after 5-10 mins, I see a lot of them still on my system. What are these tables? They also have negative ID value as you mentioned before. Are they generated by system? Are they used by my application? How do I get to know about them?”
I think my friend you have brought a different dimension to this article. I am glad you found time to write to me and I appreciate each and every one who takes time to write to me. There is no simple questions in this world. I think this is yet another example of complex SQL can be and how we can learn from SQL Server every single day.
Now let me explain you what these temp objects are which my friend is talking about. These are also temp tables created by user but inside a Stored Procedure. To illustrate the same I am going to show you inside a code block below:
Let us first create the stored procedure that creates a temp table inside the SP:
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 what ever 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 us open two sessions and execute the stored procedure in one window and then execute the objects created inside TempDB on the second window. The two session windows are:
Session 1: Run the SP
-- Execute the SP in Session 1
EXEC TempTable_Objects
WAITFOR DELAY '00:00:05'
EXEC TempTable_Objects
Session 2: Track the Object creation inside TempDB
-- 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 for the same look like:
As you can see, the object name is similar to the previous article. But unlike the standard temp table creation, here SQL Server is caching and keeping this so that the subsequent users can reuse the same. Hence in this particular case, since my friends said he saw a number of such values in TempDB, it means there are lots of temp tables created via the stored procedure which are still there post the session ending. The temp table object ID confirms our assumption that it is getting reused. On further quizzing my friend, I found there were reporting queries which are generating temp tables every 15-20 mins for in a scheduled manner. These were a constant behavior in their environment.
If you check SQL Server Management Studio Object Explorer, we can see these objects there too. Here is a typical snapshot of the same.
As I said before, these are great learning experience for me too and hope you learnt something new today with me in this exploration.
Reference: Pinal Dave (https://blog.sqlauthority.com)
