How busy is Tempdb in MSSQL

  • Often tempdb is the most used database, especially in a large production environment.
  • SQL server keeps tracks of usage since it's start with the dynamic management view: dm_io_virtual_file_stats.
  • dm_io_virtual_file_stats gets reset every time SQL Server is restarted.

Applies to:

  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Tempdb usage

  1. SELECT CAST(DB_NAME(mstr.database_id) AS VARCHAR(24)) AS 'Database',
  2.        CAST(mstr.physical_name AS VARCHAR(72)) AS 'File',
  3.        stats.num_of_reads,
  4.        stats.num_of_bytes_read,
  5.        stats.num_of_writes,
  6.        stats.num_of_bytes_written,
  7.        stats.size_on_disk_bytes
  8. FROM sys.dm_io_virtual_file_stats(null,null) AS stats
  9.          join sys.master_files AS mstr
  10.               ON mstr.database_id = stats.database_id
  11.                  and mstr.FILE_ID = stats.FILE_ID
  12. WHERE DB_NAME(mstr.database_id) = 'tempdb' or
  13.       DB_NAME(mstr.database_id) = 'sql911';
  14. go
Database                 File                                                                     num_of_reads         num_of_bytes_read    num_of_writes        num_of_bytes_written size_on_disk_bytes
------------------------ ------------------------------------------------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
tempdb                   c:\syv\tempdb.mdf                                                        61                   499712               2                    16384                11534336
tempdb                   c:\syv\templog.ldf                                                       7                    393216               6                    92672                524288
sql911                   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911.mdf      154                  6045696              1                    8192                 20709376
sql911                   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911_log.LDF  7                    393216               6                    77312                516096

(4 row(s) affected)
  • I always compare the the tempdb database againt the production database. It gives a clearer view of what's happening.