TempDB Files

Issue description

This check determines whether the TempDB database is configured properly and according to the best practices.

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. It 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, and each tempdb data file should be set to the same size. Separate database data and transaction log files across different disks. If data and log files must share disks due to space limitations, put files that have different usage patterns on the same disk to minimize concurrent access requests.

Solution

Check the database file distribution. To do so, open the SQL Server Manager on the database server and check properties for every database. The page File in the Properties window contains the information about physical location of the database files. In 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: