Applies to:
Summary
This article describes how to run a SQL Server instance health check analysis of TempDB’s in SQL manage instance tool.
Description
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
