Microsoft’s whitepaper on

Optimising TempDB in SQL Server 2005 contains a lot of useful information
for anyone trying to push the performance of SQL Server 2005. Along with the

internal improvements to tempdb, there are a number of things a database
administrator should do as part of any installation:

  • Increase the number of data files in TempDB to match the number
    of CPUs configured for the SQL Server 2005 instance. Each data file should
    be the same size.
  • Set the starting size of TempDB to an appropriate value. Use your
    existing TempDB size as a guide.
  • Enable AutoGrow for TempDB’s log and data files to handle
    unexpected growth. Set filegrowth to an approriate increment: 10% for for a
    filesize of 500MB or more
  • Place TempDB on dedicated disks, separated from all other
    databases.
  • Monitor TempDB’s space usage with the following performance counters:
    • SQL Server:Databases: Log File(s) Size(KB) (tempdb)
    • SQL Server:Databases: Log File(s) Used (KB) (tempdb)
    • SQL Server:Databases: Data File(s) Size(KB) (tempdb)
    • SQLServer:Transactions: Free Space in tempdb (KB)
    • SQLServer:Transactions: Version Store Size (KB)
    • SQLServer:Transactions: Version Generation Rate (KB/s)
    • SQLServer:Transactions: Version Cleanup Rate (KB/s)
  • Monitor TempDB’s I/O performance with the following performance counters:
    • PhysicalDisk: Avg. Disk Queue Length
    • PhysicalDisk: Avg. Disk Sec/Read
    • PhysicalDisk: %Disk Time
    • PhysicalDisk: Disk Reads/Sec
    • PhysicalDisk: Disk Writes/Sec
    • SQLServer:Databases: Log Bytes Flushed/sec
    • SQLServer:Databases: Log Flush Waits/sec
  • Monitor contention with the following performance counters:
    • SQLServer:Access Methods: Worktables Created/sec
    • SQLServer:Access Methods: Workfiles Created/sec
    • SQLServer:Access Methods: Worktables From Cache Ratio
    • SQLServer:General Statistics: Temp Tables Creation Rate
    • SQLServer:General Statistics: Temp Tables For Destruction

A Microsoft Word copy of the whitepaper is

available as well.

I’ve created a
Performance
Monitor html template that includes all the above performance counters. To
use the template, save
this page
to your computer. You can import it into Performance Monitor by right clicking
Counter Logs and choosing

New Counter Settings From. Remember to re-add the Physical Disk counters for
the drive that TempDB resides on.

Tags: Best Practices, Microsoft SQL Server, MSSQL, MSSQL Performance, SQL Server, SQL Server Management Studio, T-SQL, T-SQL Performance