TempDB Response Times

TempDB Response Times best practices report by SPDocKit determines whether the TempDB database response time is too high which can introduce severe performance issues.

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.

This T-SQL script can help you check TempDB response times:

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
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
INNER JOIN master.sys.master_files AS files
    ON stats.database_id = files.database_id
    AND stats.file_id = files.file_id
WHERE files.type_desc = 'ROWS'

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:

• Best practices for SQL Server in a SharePoint Server farm

• Move System Databases

• How to troubleshoot SQL Server performance issues