Optimizing TempDB: Avoiding Bottlenecks and Performance Issues

As the name suggests, TempDB is a temporary workspace required by SQL Server for creating and holding intermediate and temporary objects. 

TempDB is a significant cog in the overall SQL Server apparatus and as a temporary workspace – the data it holds is transient in nature. In other words, your SQL Server instance recreates TempDB every time it restarts – giving itself a clean scratch pad with which it can work.

• temporary objects triggered by user requests
• objects required by internal system processes
• row versioning information 

Needless to say, if TempDB is not configured optimally, it can lead to operational bottlenecks and a degradation in performance. It may leave you wondering why your queries with complex joins and sorting operations are not producing results as fast as expected.

There are is no easy way to generalize about the best practices for optimizing TempDB, the scenarios are too diverse and what works in a given situation may not work in another. Even if your database has gone into production, it is always a good idea to continue to review your TempDB setup to ensure it's configured as it should be.

One of the most serious issues in database performance is TempDB contention. This happens when multiple resources require TempDB, but there is only a single TempDB data file to access.  

TempDB contention can cause serious performance issues and is often misunderstood to be normal blocking due to database locks. Many times, it is actually latched contention on the allocation pages by concurrent processes. This can lead to bottlenecks as each process waits for its turn. As the turn doesn’t come fast enough, the underlying connections timeout and the processes need to be deallocated. 

What do you get? A virtual traffic jam of blocked processes.

How do resolve TempDB contention and optimize SQL Server performance? Let’s take a look at the basics and work our way from there. 

Number of data files – How many should I have?

When you set up SQL Server and keep the default configuration, you only have a single data file for TempDB.  Don’t be content with this configuration.

One of the often-touted rules of thumb is a single data file per core. But proceed with caution in this instance, if your server has 12 cores, then don’t use 12 TempDB data files unless justified by the application and load requirements.

The best option, given today’s hardware configurations, is to start with 8 equally sized primary data files and see if the contention issue is resolved. Work your way upwards and add more four files if needed. The installation and setup wizard of SQL Server 2016 has a built-in feature that ensures you have a sufficient number of TempDB data files by detecting the number of CPU cores and automatically creating the appropriate number TempDB data files. 

Size does matter – How should the data files be configured for size?

Now that we have covered the number of files, let’s take a look at the recommended size of each file. The default size is 8 MB, which provides SQL Server with a total of 64 MB of TempDB space, insufficient for most production environments.  Keeping Autogrowth is also an option, but SQL Server will have to pause, and allocate more disk space for the TempDB files when required – adding significant overhead to SQL Server during a production run.

The recommended practice is to keep files and the initial space required for each file to be roughly 80 to 90% of the volume on which the TempDB is stored. The 10 to 20% disk space is left for OS-based virtual memory.

In other words, pre-size the data files during setup or change the size of the files in the production environment. This will ensure enough disk space is allocated for TempDB. Keeping the Autogrowth option to on is always recommended at this point, but ideally, try to make sure SQL Server doesn’t have to allocate additional disk space on the fly too often. 

An interesting fact, before SQL Server 2017, it was not possible to allocate more than 1 GB per TempDB data file at the time of setup. With the latest version, it is possible to allocate as much as 256 GB for a TempDB data file during setup.

Which brings us to the next question:

Where do I keep the TempDB data files?

Prior to SQL Server 2012, in the case of a clustered environment, TempDB had to be located on disks shared between the clustered environment, like a storage area network (SAN). Beginning with SQL Server 2012, it is possible to keep the TempDB data files on SSD based local storage. This cuts down on what would have been a lot of traffic between the shared SAN and the SQL Server instance. 

In most cases, the best option for TempDB location is a dedicated local SSD. If that is not possible, then keeping it on a dedicated volume of its own, with sufficient disk space pre-allocated should solve likely performance issues. Make sure you constantly monitor disk health so that disk reads and writes are carried out at an optimum level.

Ideally, the media should be the fastest possible given the server configuration, application requirements and last but not the least, the allocated budget. 

Now that we have had a look at the basics, let’s look at relevant and welcome additions to various SQL Server additions after SQL Server 2012.

What else is new?

SQL Server 2016

Dedicated Tab During Setup

• With this edition, SQL Server has a dedicated tab for TempDB configuration during the setup workflow
• The installation and setup wizard of SQL Server 2016 has a built-in feature that ensures you have a sufficient number of TempDB data files at the time of installing SQL Server. It detects the number of CPU cores and automatically creates TempDB data files, subject to a maximum of 8. You can increase this number after setting up SQL Server.
  

Instant File Initialization 

• SQL Server has to allocate disk space for TempDB during initial setup as well as when the file size grows in a production run. This allocation can be possible in two ways. The first way is by initializing unused disk space by writing zeroes before allocating the space. The second way is by instantly allocating file space for TempDB growth. 
• In the first method, SQL Server has to carry out a disk intensive operation by initializing each logical disk cluster. In this method, the processes running on the server that need TempDB may have to wait, creating a bottleneck. 
• If you choose to instantly allocate file space instead, SQL server instantly allocates space for Autogrowth without initializing disk space. This reduces disk i/o every time there is an Autogrowth requirement and ensures better throughput and performance. Although it was possible to turn IFI on in previous editions it was a cumbersome process. In this edition of SQL Server, it is easier to setup IFI at the time of server setup.
  
• Trace flags 1117 & 1118 are redundant 

SQL Server 2017

• Before SQL Server 2017, it was not possible to allocate more than 1 GB per TempDB data file at the time of setup, meaning the TempDB file size had to be increased after the setup was complete.  With this version, it is possible to allocate as much as 256 GB for a TempDB data file during setup. 
  

Monitoring TempDB

Keeping track of TempDB can be difficult. How can you tell if you are having TempDB contention? What is being allocated to user objects, version store or internal objects? How are these trending over time? What sessions are consuming TempDB and to what degree? Spotlight Cloud makes answering these questions easy. It monitors all aspects of your SQL server performance 24/7 and provides deep dive analytical workflows to tackle any performance problem. Track your TempDB over time and get automated expert advisories on configuration. 

As a SaaS solution, Spotlight Cloud is easy to set up and configure. It retains up to a year's worth of performance data giving unbeatable tuning insights. Performance issues can be resolved in seconds with root cause analysis. Don’t waste any more time digging through complex scripts – start your 30-day trial now. Top-notch SQL Server performance monitoring starts now.