Optimizing SQL TempDB for SharePoint

Background

The TempDB is a system database in Microsoft SQL Server and is available to all users connected to the SQL instance.. It is used to temporarily store user objects that are explicitly created. For example, global or local temporary tables, temporary stored procedures, table variables, or cursors. Tables and procedures that are stored in the TempDB are automatically dropped on disconnect and the system always starts with a clean copy of the database.

For more information on the TempDB, check here.

Many administrators overlook how TempDB affects performance in SharePoint. According to the SharePoint 2010 storage optimization guide, SharePoint farm performance can be significantly impeded by insufficient I/O for tempDB.

Recommendations

Here are some tips from Microsoft on improving TempDB performance.

1. It is therefore recommended that the TempDB database is moved to your fastest disk. This should preferably be on a RAID 10 array.
2. Preallocate space for all tempdb files by setting the value large enough to accommodate the typical workload in the environment.
3. Create as many files as needed to maximize disk bandwidth. Using multiple files reduces storage contention. The guideline is 1 TempDB per CPU.
4. Make each data file the same size to allow for optimal proportional-fill performance.
5. Put the TempDB on a seperate disk to those that aused by user databases.

How to move the TempDB database

Open Microsoft SQL Server Management Studio

Click on New Query and paste the following code in, replace the paths to suit your environment:

USE master
 GO
 ALTER DATABASE TempDB MODIFY FILE
 (NAME = tempdev, FILENAME = 'c:\Databases\tempdb.mdf')
 GO
 ALTER DATABASE TempDB MODIFY FILE
 (NAME = templog, FILENAME = 'c:\Databases\templog.ldf')
 GO