I realise the world and his wife has written articles, blogs and graffiti about tuning TempDB, but I had to search around a lot of places to find out the full picture. So, I thought I’d try and pull it all together into one place.
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:
- Snapshot isolation and read committed snapshot (RCSI)
- MARS
- Online index creation, rebuilds with SORT_IN_TEMPDB
- Temporary tables, table variables, and table-valued functions
- DBCC CHECK
- LOB parameters
- Cursors
- Service Broker and event notification
- XML and LOB variable
- Query notifications
- Database mail
- User-defined functions
- Sort
- Hash match
- Spool
- Version store
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.
2. Implement Instant File Initialization – Windows 2003 and above and SQL 2005 and above allow SQL Server files to be initialized instantaneously. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Log files cannot be initialized instantaneously. http://msdn.microsoft.com/en-us/library/ms175935.aspx
3. Implement Trace Flag -T1118
http://support.microsoft.com/kb/328551
4. Pre-size TempDB – By default TempDB is created with one data file of 8MB and one log file of 0.5 MB. When SQL Server is restarted TempDB is recreated and will return to the original size. Most production systems will need much more space in TempDB. By default both data and log files of TempDB are allowed to grow automatically. Keep in mind that allowing TempDB to Autogrow will take up some system resources. After a restart a heavily used TempDB which has not been pre-sized will have to grow very frequently causing significant overhead.
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
http://msdn.microsoft.com/en-us/library/ms175527.aspx
Change TempDB data file size (1GB)
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
5. Spilt TempDB data files – Only one file group in TempDB is allowed for data and one file group for logs, however you can configure multiple files. With SQL Server 2000 the recommendation is to have one data file per CPU core, however with optimisations in SQL Server 2005/2008 it is now recommend to have 1/2 or 1/4 as many files as CPU cores. This is only a guide and TempDB should be monitored to see if PAGELATCH waits increase or decrease with each change.
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.
http://technet.microsoft.com/en-us/library/cc966545.aspx
Add file to TempDB filegroup (with Autogrow OFF)
USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N‘tempdev2′,
FILENAME = N‘D:\Data\tempdev2.ndf’ , SIZE = 10240KB , FILEGROWTH = 0)
GO
