Perform TempDB health check on SQL Server instances (4248394)

This article describes how to run a SQL Server instance health check analysis of TempDB’s in SQL manage instance tool.

 To store temporary data such as functions, indexes, views, and table data, SQL Server uses TempDB. In the majority of scenarios, the configuration of TempDB is left as default which is not necessarily a bad decision but default settings of TempDB can lead to increased performance issues of the SQL Server instance. With ApexSQL Manage, key areas of TempDB settings can be analyzed and configured to increase the overall performance of SQL instances.

Performing a TempDB health check analysis on the target SQL instance

Go to the Health tab and click the Run button to start the TempDB health check.

Selection of SQL Server instances

Run health check window encompass three tabs:

• Servers
• Rules
• Summary

In the Servers tab check a SQL instance for analysis, and move to the next tab:

• To discover new SQL instances with ApexSQL Manage, refer to the article How to discover SQL Server instances, SSRS, SSAS and SSIS services across the network

Selection of health check rules

Health check analysis is performed by executing specific rules in the Rules tab. Check the TempDB category and move to the next tab:

TempDB category consist of the following rules:

• TempDB log and filegroups files must be on separate HD drives – This rule checks if each TempDB file is on a separate drive
• TempDb data files and processor cores number must be same – This rule checks if the TempDB data files match the number of processor cores
• TempDB Simple Recovery Mode must be enabled – Recovery model of TempDB should always be set to Simple
• TempDB Response time (writing and reading) must be above 20 ms – This rule checks if the read and write response time for TempDB is less than 20 ms
• TempDB log and data files must have reasonable growing size – This rule checks if TempDB log and data files have unreasonable growth size
• TempDB size should be set to min 10% size of largest database on server – This rule checks if the TempDB size is larger than 10% of the largest database on the SQL Server
• TempDB data files should be on a dedicated drive – This rule checks if TempDB files are located the same drive as remaining SQL Server binaries

Summary of TempDB health check

The Summary tab can be used to review the health check parameters. Click OK to execute the TempDB health check:

Result summary

After every health check, the Results summary window is shown. Rules that were specified in the health check are comprised in a form of a graphical representation which can be used to showcase the health state of SQL Server instances:

Health check results

Executed rules are shown in the Rule grid and the Result column indicates if the rule has passed or failed. To provide a better overview of the key areas for improvement, drag the column Result in the group by box.

Now, the rules in the grid are grouped by the result and easily manageable:

• For detailed information on the TempDB subject, refer to the article Executing a TempDB health check analysis of SQL Server instances