DMV Queries - 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 ;




ċ
General TempDB and Wait Stats.sql
(1k)
Andy Hughes,
Jul 2, 2012, 10:04 AM
Comments