Example of creating internal temp object

Question

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-session-space-usage-transact-sql?view=sql-server-ver15

any example /demo script to generate usage of internal temp db object ?
what's the meaning of user_objects_deferred_dealloc_page_count???

Answer 1

Below is an example of a query creating an internal temp table. This is created for a sort, where not enough memory was granted when the query plan was generated. Check the execution plan and you see a warning for the sort operator, that it spills to disk in tempdb. (You probably need to increate the row count for the SELECT in the INSERT statement in order to see any actual spill to tempdb and not just the warning.)

USE Adventureworks
DROP TABLE IF EXISTS t5

CREATE TABLE t5(c1 int IDENTITY, c2 varchar(4000))

INSERT INTO t5 SELECT TOP(50000) REPLICATE('b', 4000) FROM sys.columns AS a CROSS JOIN sys.columns AS b

SELECT TOP(10) * FROM t5

EXEC sp_tableinfo 't5'

BEGIN TRAN
SELECT * FROM t5 ORDER BY c1

ROLLBACK

Answer 2

what's the meaning of user_objects_deferred_dealloc_page_count???

The BOL says Number of pages which have been marked for deferred deallocation. To understand this you need to first understand a feature called as "deferred drop" which was introduced in SQL Server 2000. To put it simply if you drop or truncate large table it will show you dropped immediately or truncated immediately but internally it uses deferred drop and removes pages by this mechanism. Quoting from Blog by Paul randal

The deferred-drop mechanism simulates the DROP or TRUNCATE operation completing immediately, by unhooking the allocations for the table and putting them on the ‘deferred-drop queue’, for later processing by a background task. This unhook-and-transfer operation only generates a handful of log records. This is the operation that is being done and rolled-back in my code example above.
The ‘deferred-drop background task’ spins up every few seconds and deallocates all the pages and extents on the deferred-drop queue in small batches, guaranteeing that the operation will not run out of memory. These deallocations are all fully-logged, but remember that deallocating a page full of data or index records does not log individual deletes of those records; instead the entire page is just marked as deallocated in the relevant PFS (Page Free Space) allocation byte-map.

From SQL Server 2000 SP3 onwards, when you perform a DROP or TRUNCATE of a table, you’ll only see a few log records being generated. If you wait a minute or so, and then look in the transaction log again, you’ll see thousands of log records have been generated by the deferred-drop operation, each deallocating a page or extent. The operation is fully and efficiently logged.

I hope it is clear now.

any example /demo script to generate usage of internal temp db object ?

Let me see if I can find some script online, but frankly speaking i have not seen one