TempDB Response Times

Issue description

This check determines whether the TempDB database response time is too high which can introduce severe performance issues.

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.

Response times for tempdb database should be less than 20 ms.

T-SQL Script (Please note that this is only a part of the script. The whole script can be found here: TempDB Performance and ConfigurationWhat Happens in SQL Server’s TempDB?.)

SELECT files.physical_name, files.name, 
stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS 
avg_write_stall_ms, 
stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS 
avg_read_stall_ms

Solution

If you are experiencing performance issues, you might want to check whether the tempdb database files are stored on dedicated drives. Also make sure that the tempdb files are stored in the fast storage, for example RAID 10 drives and / or SSD drives. Move the tempdb files to dedicated storage, if required.

To check the settings on the tempdb database, start SQL Server Management Studio and connect to the SQL server instance which hosts your content databases. In the Object Explorer tree, navigate to Databases > System Databases, select the tempdb database, right-click on it and click Properties. In the Database Properties window, select Files page.

If tempdb database files are stored in dedicated storage and you are still experiencing performance issues, you will have to perform troubleshooting on the SQL Server. One of the good starting points is the Microsoft Knowledge Base Article 298475: How to troubleshoot SQL Server performance issues.

Additional information

Additional information can be found in the following articles: