Product articles
SQL Monitor
SQL Server Performance Monitoring
Monitoring TempDB Contention using…

Monitoring TempDB Contention using Extended Events and SQL Monitor

When the tempdb database is heavily used, processes in any database on the instance will be forced to wait, due to contention as the SQL Server engine tries to manage allocation pages in tempdb. Phil Factor shows how to monitor for signs of trouble.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Sometimes, a database will slow down exponentially when it is working hard, with many concurrent users. Well, to be more accurate, all the databases on the instance will slow down.

As the number of concurrent users increases, SQL Server is designed to slow down ‘gracefully’, meaning linearly. The more recent the version, the better it conforms to this ideal. Occasionally, though, when the tempdb database is heavily used, processes in any database on the instance will be forced to wait, due to contention when the SQL Server engine tries to allocate pages in tempdb.

If high demand is intermittent, this doesn’t cause a big problem, but with persistent high demand and increasing contention, those queries and requests that involve tempdb can become unresponsive. This problem was much more common before SQL Server 2016, which is now able to make automatic configuration changes in response to this problem.

I’ll show you how to capture latch waits, the most telling wait type for detecting stress in tempdb, using an Extended Events session, and then track it over time, using a SQL Monitor custom metric.

Are you suffering from tempdb contention?

In SQL Monitor, there are a few tell-tale signs. You might notice that, for several databases, response times are longer and disproportionate to the load. On investigation, you might notice severe blocking when the server is under heavy load. When using performance diagnostics, you see PAGELATCH_SH and PAGELATCH_UP wait types emanating from tempdb. When you recognize these symptoms, it likely that the contention is caused by too many processes fighting to access data in tempdb.

It isn’t just the creation and deletion of many table variables, local temporary tables, or global temporary tables that can cause the logjam. Many other database operations are likely to make demands on tempdb, which can sometimes lead to contention, such as:

  • The use of queries with a GROUP BY or ORDER BY clause.
  • Joins and aggregations that use the HASH MATCH operator.
  • Tables that are accessed via cursors.

Any query operation where the optimizer needs, or chooses to use, a temporary worktable will require resource allocation in tempdb. For example, the optimizer uses worktables for HASH MATCH joins (and aggregations); MERGE JOINs for many-to-many join conditions require a worktable; any “Spool” operation, where the optimizer wants to store certain results for reuse elsewhere in a plan (e.g. the results of aggregation calculations) will use a tempdb worktable and you’ll often see spool operators in the plans for queries that use Window functions, Common Table Expressions, and others.

While it is fair to make tempdb work hard to resolve perfectly valid query strategies, sometimes it is abused by poor coding practices, leading to avoidable contention. I’d advise avoiding any cursor-based or more general RBAR access, for example. Also, the symptoms can appear very quickly if there are iterative constructs in the code within which a table variable is created, used and deleted. It is usually easy to reengineer the code to use a single temporary table. Many problems are caused by explicitly holding more data in temporary tables or table variables than is ever used, just to avoid the tedium of devising a suitable filtering WHERE clause or specifying just the columns necessary. You need also to be aware of doing this crime unintentionally by performing unnecessarily big sort operations (or aggregations) that spill to tempdb.

You can confirm that requests for tempdb data, or other tempdb tasks, are waiting via SSMS, and you can use the two Dynamic Management Views (DMVs) sys.dm_exec_requests or sys.dm_os_waiting_tasks to give you the “real time” wait profile of a SQL Server instance (the active requests and sessions associated with currently-suspended threads). You can also use sys.dm_os_wait_stats to get the historical perspective on your common causes of waits.

The nature of the problem

How can this contention happen in a system that is inherently parallel and multi-user? Well, to ensure the integrity of the software devices that allocate resources, SQL Server must inevitably “serialize” certain processes.

The PAGELATCH_XX waits that are often symptomatic of tempdb contention are non-IO waits to attain latches on data pages that are in-memory (i.e. in the buffer pool). Whereas locks preserve data integrity during concurrent user transactions, latches are ‘lightweight synchronization primitives’ that SQL Server uses to protect the integrity of its data structures. So, for example, to modify a data page in memory, a thread must acquire an exclusive latch on it, which is released once the operation has finished. Any other thread wishing to access that same page, in the meantime, must wait. Likewise, a thread that is reading the data page acquires a shared latch, which will prevent another thread changing it. If a lot of processes want the page, there will be contention; a queue will develop, and processes will wait in line. If processes take longer to use the page, the same thing will happen.

Latch contention can happen for several reasons in any hard-working database, such as due to an “insert hotspot” on a clustered index with an IDENTITY key. However, it can sometimes be a particular problem in tempdb, where you can have lots of concurrent sessions, in various databases, all attempting to create and destroy objects in tempdb, and so all attempting to modify various of its in-memory data pages. The contention ‘hotspots’, in this case, are the allocation map pages that SQL Server uses to track use of the data pages and which pages are allocated to which objects and which databases. Every time a tempdb data page is modified, SQL Server must also modify the relevant allocation pages, each time requiring an exclusive latch, which again means this is a serial operation. When lots of small objects are being created and destroyed, then lots of threads need simultaneous access to the first few allocation pages in the tempdb data file, and queues form.

To dive briefly into the details, the problem is that SQL Server Engine must use the Shared Global Allocation Map (SGAM) page when a data object is created or deleted. On creation, it needs to assign to it two data pages from a mixed extent. These are the Index Allocation Map (IAM), and the first page for the object. In order to find these two pages, it must scan the Page Free Space (PFS) page to determine which mixed page is free to be allocated. The PFS page keeps track of free space available on every page, and each PFS page tracks about 8000 pages. Each SGAM page tracks about 4 GB of data.

When SQL Server searches for a mixed page to allocate, it always starts the scan on the same file and SGAM page. Both the PFS and the SGAM pages need to be updated at the same time to perform the allocation and so if several processes are hard at work in tempdb, they may have to wait their turn for short periods before they can create or remove data objects. This can, if the queue is served slower than it grows, cause delays to increase.

Easing the contention

Recent versions of SQL Server suffer far less from this problem. In addition to the round-robin allocation across all tempdb data files that was introduced in SQL Server 2016, PFS page allocation is also less liable to contention now that round-robin allocations are performed across several PFS pages in the same data file.

It remains a good policy to increase the number of data files in tempdb to maximize disk bandwidth and reduce contention in allocation structures. Generally, if you have eight or less logical processors then use the same number of tempdb data files as logical processors; Otherwise use eight data files. If you continue to see contention, then increase the number of data files by multiples of four up to the number of logical processors.

If you are using an old version of SQL Server, then Enable trace flag 1118 as a start-up trace flag.

However, when employing the above strategies, don’t forget that if the problem is caused by large and frequent queries that employ bad coding practices, then even the most skilled configuration work can only paper over the cracks. You should also examine the routines and batches that are associated with the problem and look for, and re-engineer, any code smells that are likely to be exacerbating the problem.

Monitoring a server estate for TempDB problems

If you query the sys.dm_os_waiting_tasks DMV with a filter like this…

WHERE wait_type LIKE 'PAGE%LATCH_%' AND resource_description LIKE '2:%'

…you’ll see a snapshot of currently active requests and sessions associated with suspended threads that are waiting to obtain a latch on tempdb resources.

However, whatever other evidence you have, the most important information to have is a good baseline for tempdb activity, which captures over time the typical profile for tempdb latching for your workload.

I find that the most telling metric for picking up stress in tempdb is the total duration of latch waits over a time period (I use one-minute time intervals). I’ll show you how to capture this data using an Extended Events session and then track it over time, using a SQL Monitor custom metric. This will give us our baseline.

Figure 1: Tracking latch wait times in SQL Monitor using a custom metric

If your baseline for this metrics varies around a particular figure then you’ll quickly notice anything unusual, because if the number of latches increases, or the duration of latch waits increases, or both, it will affect the total duration over the interval.

At this stage, you don’t want the detail, you just need to know that something unusual is happening. The easiest problem to solve is an intermittent one, where there are rogue processes that swamp tempdb. It is quite hard to increase the value a great deal, if you have a lot of well-written routines running concurrently, but a rogue query can cause a very clear spike.

It is always valuable in performance-testing to give the database a realistic workload that includes the routines that are suspect and look for a spike in the duration of latches. When I’m doing development work, I find SQL Monitor a very handy tool for performance testing because it can allow you to spot problems, and then you can home in on the detail by querying the underlying event data directly. Figure 2 shows the results of using the query I provide later (in Listing 3).

Figure 2: tempdb latching profile over the recent period

Monitoring latch waits using Extended Events

First, we need to set up an Extended Events session to capture the data we need. The sqlserver.latch_suspend_begin event fires when a thread is suspended while waiting for a latch, and sqlserver.latch_suspend_end fires when it resumes.

In fact, we only need to collect the latch_suspend_end event because its duration event field also tells us how long the requesting thread had to wait, in microseconds, for the latch. We can filter to track only allocation contention inside tempdb.

Listing 1: Create the MonitorTempDBContention event session

My goal here is to get the total duration of latch waits, in the last minute, so we need to collect all the event data, without any filter except for the database.

It might be useful to collect additional event data such as the session_id and sql_text associated with the session that was blocked, waiting for a latch on tempdb. If you wish, you can add these two global fields, as actions, to the above event session definition. You’ll need to test it out, gauge how much data it collects over a period, and how much overhead it will cause to have it running continuously.

Here, I’ve kept the event session minimalist, collecting only one event, and no additional actions. Later in the article, I’ll show another event session that only collects those events where the duration is significant and collects the additional event data, for investigating the blocked sessions.

The monitoring metric: total duration of latch waits in a minute

With this event session running, we can devise a query, using the collected event data, to find out the total latch wait duration over the previous minute (actually, I use the minute before the previous minute, just to be certain).

Listing 2: Total latch wait time over the previous minute

If you want to test this out on your local instance, you’ll need to generate a bit of tempdb action. Listing 7 in Jonhathan Kehayias’ article, Optimizing tempdb configuration with SQL Server 2012 Extended Events will do the trick. Alternatively, you can use Barry Young’s or Jeff Moden’s TempDB Stress Tester from here, or Adam Machanic’s SQLQueryStress.

Installing the custom metric in SQL Monitor

Having created and started the event session, we can install the query in Listing 2 as a custom metric, called MonitorTempDBContention, in SQL Monitor. If you click on the previous link, you can install the metric directly from the website.

You need to collect the data from tempdb only, every 1 minute. The appropriate alert thresholds are entirely dependent on your system, and you should only set them after establishing a baseline for your working instance. In my case, after some testing, I set it to raise a low alert with a threshold of 1500000, but this varies so much with the system that I’m not going to suggest that it is a good value to use.

In the Analysis section of SQL Monitor, you can plot the metric over time, and you’ll see a graph like the one in Figure 1. If you spot odd spikes in latch wait duration you might as a first step use similar code to Listing 2, but this time getting a bit more detail.

Listing 3: Total per-minute latch times, and other metrics, over the recent period

Which will give results that will look somewhat like this

Figure 3: tempdb latching over the recent period

The event has more fields, however, and some of them are useful but at this stage we’re just interested in the broad landscape.

Figure 4: Event fields for the sqlserver.latch_suspend_end event

Investigating the causes of Latch contention

When you receive alerts, or spot abnormal patterns of behavior, you can start to drill into the details to discover the cause of the tempdb contention. We’ll want to drill into the detail to see what tasks are getting caught as a ‘contention victim’. When I was testing this, I found to my embarrassment that one of my scheduled tasks was causing contention in tempdb due to an injudicious query that cause the SQL Server optimizer to use a HASH MATCH join.

In Listing 4, I create a slightly modified version of our original event session that gives us more detail about a more select group of contention victims. We will just look at the queries that get held up for more 20 microseconds, but it could be a lot higher in an active system. We only want to see the out-and-out rogues. I’ve also collected some additional event data to capture the details we need about the blocked sessions, and what SQL they were executing.

Listing 4: The TasksDelayedByTempDB event session

It is useful to have the session_id, because it is then possible to look in a lot more detail but for my purposes, it is good enough to eyeball the query itself rather than, say, looking at the query plans. Listing 5 shows a query that will tell you about the sessions being caught up in tempdb contention. I’ve given you more columns than you’ll need but you can just comment out what you don’t need.

Listing 5: Investigating tempdb contention


As database systems take on increasingly heavier loads, there will inevitably be places where data structures must be accessed in a serial fashion within the database engine. This can introduce contention points. This is especially so in tempdb where a great deal of concurrent operations take place. This need not need to be a special problem if the SQL queries that use tempdb are reasonably well designed and operate with a reasonable query plan.

SQL Monitor helps by establishing a baseline and allowing us to spot those times where high contention happens. We can then use extended events to drill into the detail, find the culprits and deals with the problems. There are several tools, techniques and ways to approach this exercise as well as practices that can be followed in re-engineering applications to avoid them altogether.


Tools in this post

SQL Monitor

Real-time SQL Server performance monitoring, with alerts and diagnostics

Find out more