TempDB Files Configuration

TempDB Files Configuration best practices report by SPDocKit determines whether TempDB database files are configured properly.

Issue description

This check determines whether TempDB database files are configured properly.

Explanation

SharePoint Server uses SQL Server to store configuration and user data. SQL Server stores data over the various file types. The TempDB system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

Temporary user objects that are explicitly created, such as global or local temporary tables, temporary stored procedures, table variables, or cursors.
Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
Row versions that are generated by data modification transactions in a database that uses read-committed row versioning isolation or snapshot isolation transactions.
Row versions that are generated by data modification transactions for features, such as online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Testing and user data show that insufficient disk I/O for TempDB can significantly impede overall farm performance.

To avoid this issue, allocate dedicated disks for the drive that stores TempDB data files. For best performance, use a RAID 10 array for the drive that stores TempDB data files.

The number of TempDB data files should equal the number of CPU cores (no larger than 8), and each TempDB data file should be set to the same size. Separate database data and transaction log files across different disks.

Solution

Check the TempDB database file distribution and configuration. To do so, open the SQL Server Manager on the database server and check properties for the TempDB system database. The page Files in the Properties window contains the information about physical location of the database files.

In the case you need to move the database files to another drive or server, please read the articles referenced in the Additional information section.

Additional information

Additional information can be found in the following articles:

PreviousTempDB Files NextTempDB Recovery Model

Last updated 1 year ago

Explanation

Temporary user objects that are explicitly created, such as global or local temporary tables, temporary stored procedures, table variables, or cursors.

Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.

Row versions that are generated by data modification transactions in a database that uses read-committed row versioning isolation or snapshot isolation transactions.

Row versions that are generated by data modification transactions for features, such as online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Testing and user data show that insufficient disk I/O for TempDB can significantly impede overall farm performance.

To avoid this issue, allocate dedicated disks for the drive that stores TempDB data files. For best performance, use a RAID 10 array for the drive that stores TempDB data files.

Solution

In the case you need to move the database files to another drive or server, please read the articles referenced in the Additional information section.