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:

    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

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 ;