14 December 2010

Optimize tempdb Performance

To optimize tempdb Performance in a production environment:
1) Set the recovery model of tempdb to SIMPLE.
2) Allow for tempdb files to automatically grow as required.
3) Set the file growth increment to a reasonable size. Set it to 10% of the file size, with a minimnum of 10MB
4) create one data file for each CPU on the server, note that a dual-core CPU is considered to be two CPUs.
5) Make each data file the same size; this allows for optimal proportional-fill performance.
6) Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.
7) Put the tempdb database on dedicated disks.

For more information: MSDN Optimizing tempdb Performance

No comments: