General TempDB and Wait Stats
Applicability:
SQL Server 2000: Not Supported
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Credits:
Author: Unknown
Date: 7 May 2012
Description
These are 3 small DMV queries that don't warrant conversion into a function, but which will still be useful occsionally:
Get Free Space in TempDB
Quick TempDB summary - object sizes and free space
Signal Wait Time summary - where the process is waiting for CPU cycles
Code
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 ;