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

Next is a sample query that simply generates 10 million records by cross-joining
sys.columns catalog view with itself multiple times, and inserts the resulting data into
temporary table, #tbl_SampleData , by using SELECT INTO syntax. Because we inserted
records into temporary table, it required I/O operation to be performed on TempDB database
and SQL Server performed allocation and deallocation of space at page-level in order to
complete the request. SQL Server updates the internal statistics which are returned by
sys.dm_db_session_space_usage once the request is completed.

After the execution of the sample query is completed, the updated page allocation and
deallocation statistics are collected by querying sys.dm_db_session_space_usage
and inserting them into tbl_TempDBStats table.

Once the statistics data is gathered into the table, we then review the table data by executing
a query to examine how many pages were allocated and deallocated in TempDB database by
current session when the sample query was executed.

Finally, we fetch the space usage statistics for the file using a query that uses sys.dm_
db_file_space_usage . This view returns the number of unallocated pages, and pages
reserved by version store, user-defined objects, and internal objects. We calculate free
space and used space in MB by calculating all the deallocated and allocated pages
across all the files.

There's more...

The following section discusses the DMVs and DMFs that we used in this recipe in more detail.

sys.dm_db_session_space_usage (DMV)
sys.dm_db_session_space_usage is a the dynamic management view. This DMV
returns the total number of pages that have been allocated to and deallocated from TempDB
database by each session. Pages can be allocated to or deallocated from user-defined objects
or system objects. This DMV can be used to find the session that is making most of the
TempDB database. Following are some of the useful columns of this DMV:

Column name Description

session_id This column represents the ID of

the session.

database_id This column represents the internal ID

of the database assigned by SQL Server.
For TempDB database, database_id is
always 2 .

user_objects_alloc_page_count This column represents the total number

of pages that have been allocated to or
reserved for user-defined objects by
the session.