TempDB is a section of SQL Server instance that is used to store temporary data of tables, views, objects, functions, and indexes and is often overlooked in the grand scheme of performance optimization; hence it is left on default by DBAs. It is not an overly complicated area, but in everyday DBA tasks, this area gets a little less attention. Leaving the TempDB configuration on default will not result in a production environment crash, but it will take a toll on the overall SQL Server performance and, in worse case scenarios, jeopardize contingency plans in disaster recovery scenarios. With a little optimization management and customization in key areas, the aforementioned scenarios can be averted, and the performance of a SQL Server instance increased to mitigate the toll.
With ApexSQL Manage, a SQL Server instance management tool, potential problems can be detected by performing various health checks of SQL Server instances, among which is a TempDB configuration check. For the majority of failed health checks, there is a one-click solution where the application generates a FixSQL script to resolve the issue.
Here are the questions that should be considered when evaluating SQL Server performance concerning TempDB:
- Are TempDB log and filegroup files located on the same HD drive as the instance?
- Is the number of tempdb files the same as the number of processor cores present on SQL Server?
- What is the TempDB Recovery Model type?
- Is TempDB response time (writing and reading) below 20 ms?
- Do TempDB log and data files have an unreasonable growth size?
- Is TempDB size set to less than 10% size of the largest database on the server?
- Do data files for every available TempDB have a dedicated drive?
TempDB log and filegroups files location
Best practice if not to keep TempDB log and filegroups on the same HDD as SQL Server binaries. This is the default location when installing and configuring a SQL Server instance, but in reality, this action is considered a potential risk for the environment. Moving the TempDB files on a different disk provides a failsafe measure for these scenarios and a viable recovery plan. Executing this rule will check if there the TempDB files are located on the same HD drive. There is no FixSQL script to rectify this issue since the procedure for moving the files must be done manually:
TempDb data files and processor cores number mismatch
This is still considered to be the biggest “it depends” in the SQL community. The main reason to use multiple data files in high trafficked SQL Server instances is to increase the I/O to TempDB. Official Microsoft recommendation is to keep a 1:1 ratio of TempDB data files and processor cores. But the general rule of thumb is to increase the TempDB files to follow the number to processor cores present on the instance. This is, of course, dependent on the actual environment configuration and use cases. The number of tempdb files should be equal to the number of processor cores for 8 or fewer cores. But, for environments with more than 8 processor cores, the number of TempDB files should be equal to 1/4 ~ 1/2 of total processor cores. When increasing the number of TempDB files, make sure to increase them gradually and only to the point of alleviating contention.
Running this rule will check if the TempDB data files match the number of processor cores:
TempDB Recovery Mode check
The recovery model of Tempdb should always be set to SIMPLE. By default, the TempDB recovery model cannot be changed.
Running this rule will check the recovery model of TempDB:
TempDB Response time (writing and reading) below 20 ms
The response time of TempDB is a clear indicator that there are performance issues on the SQL Server instance. Best practice response times for TempDB read and write operations should be less than 20 ms. To remedy the issue, it is recommended to keep the TempDB database files located on a fast dedicated drive, e.g., RAID or SSD drives. Running the rule will show if the TempDB response time of a chosen instance is not in compliance with best practices:
TempDB log and data files unreasonable growth size
The key focus here is to avoid auto-growth as much as possible by pre-grow the files to the desired amount. Performance issues on the SQL Server instance can occur if growth increments are set to a low value where the Server will often result in auto-growth operations, which will cause a performance strain on that instance since it is waiting for the file to be grown. Calculate these operations daily, and the result will be a great performance decrease. The best practice is to pre-grow the files and set the file growth increment to a reasonable size to avoid the TempDB database files from growing too often as a result of small growth values.
Note: If the TempDB file size is under 200 MB, set the File Growth to Megabytes value, otherwise set the File Growth to a Percent value.
Before embarking on this endeavor, the recommendation is to determine the best average value by observing the TempDB database files and instance performance in a few days. Once the average value is determined, simply run the rule, click the FixSQL button to generate a script, and customize it accordingly. When the parameters are configured, click the Execute button to apply the changes to the instance. By default, ApexSQL Manage is checking if the TempDB files are greater than 1024 MB and in this case, FILEGROWTH is set to 10% of the total file size:
TempDB size set to less than 10% size of the largest database on the server
The initial size of the TempDB, when installing a SQL Server instance, is set to 8MB with a growth increment of 64MB. This is a drastically low setting for production environments and, if not addressed, will result in a great performance toll of said instance. The best practice is to set the TempDB size to at least 10% of the largest database on the instance, and if the performance is still taking a hit, increase the value to a point where the performance strain is diminished. In this example, the largest database on the selected instance is a total of 3360MB (MDF, LDF, and NDF files), and the recommended size in the generated FixSQL script is to set the TempDB size to no less than 336MB. Given that this instance has 4 TempDB files, each file is going to be set to 85MB:
TempDB data files not located on a dedicated drive
For SQL Server 2012 and above, the option to keep TempDB data files on an SSD drive has been implemented. This opened a new door into the performance increase of SQL Server instances. Since production environments tend to grow to meet the ever-increasing demand for free disk space, it is recommended to keep the TempDB files on a separate SSD drive dedicated solely to the TempDB data files. Following this best practice, the TempDB size can be pre-grown to a previously determined amount, and this will, in a term, minimize the need for often auto-growth operations, which can affect SQL Server instance performance and eliminate the possibility of occupying disk space needed for other applications. If the execution of the rule fails, click the FixSQL button to generate the script and specify the parameters for the new location of TempDB data files:
Conclusion
With a few tweaks of TempDB settings, the overall performance of SQL Server instances can be increased to a significant amount. Using ApexSQL Manage to examine TempDB configurations and determine the health status on multiple instances at once provides a faster and easier method of detecting potential issues and resolving them before they have a major performance impact. Enforcing TempDB best practices will ensure that peak performance is being met across all managed instances.
August 20, 2020
