Continuing on from my post describing what SQL Server TempDB is used for, I wanted to write about tempdb best practices and how on a busy system, modifications to the default setup of tempdb and its placement can boost performance.
How to optimize tempdb
- Place tempdb on a separate disk to user databases. See my post on how to move tempdb.
- Place tempdb on the fastest IO subsystem possible.
- Size tempdb accordingly and configure autogrow. This is especially important if your system cannot tolerate performance degradation. If you size the database too small with autogrow enabled, tempdb will automatically grow according to the size increments you have set up. Imagine you have set it to grow in increments of 10Mb and you have a busy workload requiring tempdb to be used. If tempdb needs to expand a number of times to accommodate load, these sizing operations can generate lots of IO. Better to try and set your tempdb to be based on a staging server workload where you have witnessed how large it can grow.
- Create multiple data files for tempdb, but how many? Allocate 1 data file per physical or virtual CPU core.
- Keep tempdb data files equally sized and have autogrow increments configured equally across data files.
- If your SAN administrator can provide it, split the multiple data files over different LUNs as opposed to holding everything on one LUN.
- Configure the data files to be of equal size.
Please note that there will be many systems out there which have had no modifications done at all to tempdb and they run fine.
Whether you choose to apply all or some of these methods will largely depend on your budget and your knowledge of the system based on projected or analysed workload. I would recommend that for any system, that you try and size tempdb accordingly however.