As the name suggests, tempdb is a temporary workspace or that SQL Server requires for creating and holding intermediate and temporary objects.
It is a significant cog in the overall SQL Server apparatus. As it is a temporary workspace – the data it holds is transient in nature. In other words, your SQ 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 optimally configured, 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. Testing your database before putting it to production use would be a good idea. Even if your database has gone to production, it is never too late to look at the underlying issues. Maybe tempdb is not configured as it should be.
One of the most serious issues in database performance is tempdb contention. This happens quite simply when too many resources that require tempdb are scrambling for and having to work with a single tempdb data file.
Tempdb contention is a serious performance issue and is often misunderstood to be normal blocking due to database locks. 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 thumb rules is a single data file per core. Use this thumb rule with caution. 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 at the time of installing SQL Server. IT detects the number of CPU cores and automatically creates 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.
That will be insufficient for most production environments. Keeping the Autogrowth on is an option. In that case, SQL Server will have to pause, catch its breath and allocate more disk space for the Tempdb files as and when required – adding significant overhead to SQL Server during a production run.
The recommended practice is to keep the product of the number of files and the initial space required for each file to be roughly about 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 a production environment. This will ensure enough disk space is allocated for tempdb. Keeping the Autogrowth option to on is always recommended, but ideally, try to make sure that 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 this 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 these 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, such as a storage area network (SAN). Starting from 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.
Even otherwise and 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 that 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 starting from 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
- 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
- This is done by writing zeroes before allocating the space. The second way is by instantly allocating file space for tempdb growth.
- If the first method is selected, SQL Server has to carry out a disk intensive operation by initializing each logical disk cluster. The fallout is that processes running on the server that need tempdb may have to wait, creating a bottleneck.
- As per the second method, 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. With this version, it is possible to allocate as much as 256 GB for a tempdb data file during setup. With versions prior to this edition, the size of the tempdb file had to be increased after setup was complete.
- 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.