DaveWentzel.com            All Things Data

tempdb Performance


moving tempdb
select name, physical_name from sys.master_files
where database_id = db_id ('tempdb');
alter database tempdb modify file (name = logical_name,
filename = "new path and name")
--do the above command for each data/log file
stop and restart sql server and verify change is correct
tempdb performance
  • contention occurs because of latches for SGAM and PFS

  • look for waitresource 2:1:1, 2:1:3
    • refers to the first PFS/SGAM page in file 1 of tempdb.  SGAM re-occurs after every 511,232 pages and each PFS recurs after every 8,088 pages. 
    • So simple math can tell us if contention occurs on these pages by looking for waitresource problems on those pages. 

  • multiple data files should be used, of equal size

  • they don't need to be on separate spindles or disks. 

  • ratio of 1 file: 1 CPU, this is b/c there is only one active thread/CPU at a time. 

  • latches are then distributed in round-robin fasion to all files, hence latching should be greatly reduced. 
  • tempdb Disk Space statistics


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

    Temporary Objects

    Add new comment