What is TempDB and why should I care?
Each instance of SQL Server has a single TempDB system database which is a shared resource for all the other databases on the instance and as such can become a resource bottleneck .
TempDB is used for lots of operations within SQL Server, including
How to find if TempDB is a bottleneck
Paul Randal (Blog | Twitter) demonstrates that you need to identify if TempDB is experiencing PAGELATCH waits: http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx
Glenn Berry (Blog | Twitter) provides a script to identify if TempDB has high PAGELATCH waits using the sys.dm_os_wait_stats DMV: http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!2991.entry?sa=873818292
The important column here is the io_wait_time_ms as it subtracts the signal_wait_time_ms - which is time spent runnable rather than actually running.
Glenn also provides a script to show which database data and log files are suffering from IO stalls - these are a sign of disk subsystem bottlenecks on the server.
How to tune TempDB
1. RAID - Place TempDB data and log files on seperate RAID 1 or preferably RAID 10 disk arrays.
It is recommend that you monitor the typical size of TempDB on your system. Once you have a good feel of the usual size of TempDB, make the TempDB data file larger than its usual size and turn off automatic growth of this data file. Ideally TempDB would reside on a dedicated disk and be pre-sized to the maximum space available.
Script to check your TempDB size and growth parameters
USE [master] GO ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 1024000KB ) GO
Change TempDB log file size (0.5GB)
USE [master] GO ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 512000KB ) GO
Adding more data files may help to solve potential performance problems that are due to I/O operations. Having too many files increases the cost of file switching, requires more IAM pages, and increases the manageability overhead. The size of each file is configurable. More files can be added, moved, or removed. To better use the allocation mechanism (proportional fill), the size of the files should be equal.
USE [master] GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'D:\Data\tempdev2.ndf' , SIZE = 10240KB , FILEGROWTH = 0) GO
Since 2005 I have worked in the UK for Quest Software as a lead consultant on their SQL Server products. I meet with customers and cover pre-sales, post sales and consultancy on all things SQL Server. I have presented at various SQL Server user groups and at SQL Bits around performance, configuration, security, disaster recovery and high availability of the SQL Server platform. Prior to this I was a production SQL Server DBA for 5 years at Framlington Group and ITouch PLC starting out on Version 6.5.
His online presences include: