General TempDB and Wait Stats

SQL Server 2000: Not Supported

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Author: Unknown

Date: 7 May 2012

These are 3 small DMV queries that don't warrant conversion into a function, but which will still be useful occsionally:

1. Get Free Space in TempDB
2. Quick TempDB summary - object sizes and free space
3. Signal Wait Time summary - where the process is waiting for CPU cycles

Get Free Space in TempDB:

-- Get Free Space in TempDB

SELECT SUM(unallocated_extent_page_count) AS [free pages] ,

( SUM(unallocated_extent_page_count) * 1.0 / 128 ) AS [free space in MB]

FROM sys.dm_db_file_space_usage ;

Quick TempDB Summary:

-- Quick TempDB Summary

SELECT SUM(user_object_reserved_page_count) * 8.192 AS [UserObjectsKB] ,

SUM(internal_object_reserved_page_count) * 8.192 AS [InternalObjectsKB] ,

SUM(version_store_reserved_page_count) * 8.192 AS [VersonStoreKB] ,

SUM(unallocated_extent_page_count) * 8.192 AS [FreeSpaceKB]

FROM sys.dm_db_file_space_usage ;

Signal Wait Time Summary:

-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)

SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms)

AS NUMERIC(20,2)) AS [%signal (cpu) waits] ,

CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms)

/ SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [%resource waits]

FROM sys.dm_os_wait_stats ;