First published on MSDN on Jun 29, 2018
TL;DR – Update to the latest CU, create multiple tempdb files, if you're on SQL 2014 or earlier enable TF 1117 and 1118.
And now it's time for everyone's favorite SQL Server topic – tempdb! In this article, I'd like to cover some recent changes that you may not be aware of that can help alleviate some common performance issues for systems that have a very heavy tempdb workload. We're going to cover three different scenarios here:
If you've been working with SQL Server for some time you've probably come across some recommendations for tempdb configuration. Here's the common wisdom when it comes to configuring tempdb:
These recommendations help address an object allocation bottleneck that can happen when your workload creates many temp tables concurrently. You know you have this type of contention if you see a lot of PAGELATCH waits on page resources 2:X:1 (PFS), 2:X:3 (SGAM), or 2:X:<some multiple of 8088> (also PFS) where X is a file number. These are special pages which are used when SQL Server needs to allocate space for a new object. By having multiple files that are equally sized, each new object allocation hits a different file, and thus a different PFS page, in a round robin fashion. It's such a common scenario that we've made these configurations the default in SQL Server 2016.
There are some cases where having multiple files alone does not completely address PFS contention. For these cases, we have implemented a fix where we not only round robin between the files, we also round robin between the PFS pages within the files, allowing us to spread the object allocations across all the files and within the files themselves. It works something like this:
Figure 1: New round robin algorithm for allocating space using PFS pages.
As you can see, this will have the effect of spreading the object allocations across the entirety of the files rather than filling them from the beginning to the end. The tradeoff with spreading the allocations throughout the file is that a shrink operation may take longer because it will need to relocate data to the beginning of the file. In fact, if temp tables are still being created during the shrink operation, you may not be able to shrink the files much at all. Hopefully you're not shrinking databases anyway so that shouldn't be a huge burden :)
With this change, not only will increasing the number of files help with PFS contention, increasing the size of the files (which increases the number of PFS pages in the file) will also help. This new behavior doesn't replace the need for multiple files, it just gives you another tool to troubleshoot PFS contention. The recommendation listed above for number of files is still valid, and in fact, if you are having I/O contention on the tempdb storage device in addition to PFS contention, having multiple files will allow you to easily balance the I/O across multiple devices if you need to.
Object allocation contention has been a common occurrence across many versions of SQL Server, but over the years with database engine and hardware improvements that increase the frequency of temp table generation, another type of contention in tempdb has emerged – metadata contention. This is contention on the system objects in tempdb that are used to track temp tables. For this issue, let me rewind the clock a little bit and give you some history. If you've been working with SQL Server for a long time (sorry I'm about to remind you how old you are :-P), you may remember this super awesome amazing version of SQL Server called SQL Server 2005. This is the version where we introduced a whole new world: the SQLOS, DMVs and temp table caching (among other things). Temp table caching was introduced because we were already seeing metadata contention in SQL Server 2000. Caching the metadata for temp tables that were created by stored procedures (and thus were nearly guaranteed to be re-used as-is) allowed us to reduce the contention we were seeing when we inserted new metadata into the system tables. This worked beautifully and allowed us to scale up for many years.
Cache isn't an unlimited resource so of course we need to periodically prune the cache to make room for new objects. Whenever we remove something from the cache, we need to delete the corresponding rows from our metadata tables. Fast forward to today – have you guessed where we are heading yet? Now we are starting to see contention on the deletes from the cache. This is a problem you may have seen in the past, but it wasn't often on a large scale. In SQL Server 2016 this problem has become more noticeable because of the increase in table metadata that comes along with some of the new features implemented in this version. Features like temporal tables and Always Encrypted require additional metadata, and even though most of your temp tables aren't going to use these features, we need to track this metadata just in case. This means when we need to prune the temp table cache, either because of memory pressure or because a cache item has been invalidated, we need to remove a lot more metadata than we did in the past.
Figure 2: Illustration of tempdb metadata contention.
To address this problem, we have made 3 main changes to how we prune the temp table cache:
So how do you know if you have this metadata contention? At first glance it may look a lot like the tempdb contention you're used to seeing – PAGELATCH waits on various tempdb pages (2:X:Y where X is a file number and Y is a page number). The difference here is that rather than PFS and SGAM pages, these will be pages that belong to system objects such as sysobjvalues and sysseobjvalues. Here are the fixes which help address metadata contention in tempdb:
Performance issues occur in the form of PAGELATCH_EX and PAGELATCH_SH waits in TempDB when you use ...
FIX: Heavy tempdb contention occurs in SQL Server 2016 or 2017
Keep in mind that even with these fixes, it's still possible to hit metadata contention in tempdb. We are continuing to work on improving tempdb performance and metadata contention, but in the meantime, there are some best practices you can employ in your code that might help avoid the contention:
The above practices help ensure that the temp tables you create within your stored procedures are eligible for caching. Temp tables that are altered
or explicitly dropped [ EDIT : While an explicit drop of a temp table in a stored procedure is unnecessary and thus not recommended, it will not invalidate the cache entry.] within the stored procedure will be marked for deletion since they can't be reused. The more temp tables that need to be deleted, the more likely you are to hit the contention described above. It's also worth mentioning here that table variables carry less overhead than temp tables and may be appropriate in some scenarios in place of a temp table. If the number of rows in the table is known to be small (rule of thumb is < 100), table variables may be a good alternative. Also keep in mind that no statistics are created for table variables. If the queries you are executing against the table variable are sensitive to cardinality changes, consider enabling trace flag 2453 to turn on cardinality-based recompiles for table variables.
Another change in SQL Server 2016 behavior that could impact tempdb-heavy workloads has to do with Common Criteria Compliance (CCC), also known as C2 auditing. We introduced functionality to allow for transaction-level auditing in CCC which can cause some additional overhead, particularly in workloads that do heavy inserts and updates in temp tables. Unfortunately, this overhead is incurred whether you have CCC enabled or not. Starting with SQL Server 2016 SP2 CU2 and SQL Server 2017 CU4, we automatically bypass this code if CCC is disabled.
FIX: Workloads that utilize many frequent, short transactions in SQL Server 2016 and 2017 may consu...
Hope this helps you optimize your tempdb workloads! Stay tuned for more tempdb improvements in future versions of SQL Server!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.