Dynamic Management Views and Dynamic Management Functions - Microsoft SQL Server 2012 Performance Tuning - page 161

,SC1.name
,SC1.system_type_id
INTO TempDB.dbo.#tbl_SampleData
FROM sys.columns AS SC1
CROSS JOIN sys.columns AS SC2
CROSS JOIN sys.columns AS SC3
ORDER BY SC1.column_id
GO

--Collect page allocation details for
--current session after executing
--sample query.
INSERT INTO [dbo].[tbl_TempDBStats]
SELECT
session_id
,database_id
,user_objects_alloc_page_count
,user_objects_dealloc_page_count
,internal_objects_alloc_page_count
,internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage WHERE session_id = @@SPID
GO

3. Now, run the following query and notice the difference in the statistics before and

after executing of the sample query:

USE tempdb
GO

--Notice the difference in page allocation
--and deallocation statistic details by looking at
--collected data.
SELECT * FROM [dbo].[tbl_TempDBStats]
GO

4. You should see a result similar to the one given in the following screenshot. Note

that the figures may vary on your system compared to what the following screenshot
is showing: