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

5. Run the query as shown in the following script to retrieve the space usage statistics

of TempDB database files:

--Get file space usage statistics by calculating
--space of unallocated and allocated pages
SELECT
DB_NAME(FSU.database_id) AS DatabaseName
,MF.Name As LogicalFileName
,MF.physical_name AS PhysicalFilePath
,SUM(FSU.unallocated_extent_page_count)*8.0/1024
AS Free_Space_In_MB,
SUM(
FSU.version_store_reserved_page_count
+ FSU.user_object_reserved_page_count
+ FSU.internal_object_reserved_page_count
+ FSU.mixed_extent_page_count
)*8.0/1024 AS Used_Space_In_MB
FROM sys.dm_db_file_space_usage AS FSU
INNER JOIN sys.master_files AS MF
ON FSU.database_id = MF.database_id
AND FSU.file_id = MF.file_id
GROUP BY FSU.database_id,FSU.file_id,MF.Name,MF.physical_name

6. Observe the result returned by the query.

How it works...

At the beginning of the first script, we first create a table tbl_TempDBStats in order to be
able to collect page allocation and deallocation statistic data for the current session. Before
creating the table, we first check if the table already exists. If it exists, we simply drop the
existing table and create a new one.

Next, page allocation statistics for TempDB database are collected for the current session
and we store them in our newly created table tbl_TempDBStats . To do this, we query
sys.dm_db_session_space_usage . We fetch total number of allocated and deallocated
pages by both user-defined objects and internal system objects. The result set returned by
the DMV is inserted into tbl_TempDBStats . Notice how we filter the result of sys.dm_db_
session_space_usage by comparing the session_id with @@SPID metadata function. @@
SPID returns the session ID for the current connection. So, only statistics data for our current
session is recorded and stored in the table.