In the Happy Database movies, SQL Server is benign and friendly, with no scary surprises lurking around the next corner. In these movies:

  • People work together
  • No-one but the DBA can make changes to production
  • People tell us in advance when they want to change something
  • When query performance degrades, we're notified about it
  • Third Party vendors only deploy good changes
  • Deployment scripts always work perfectly

Back in the real world of SQL Server, not all of these are true all of the time and the result is that we spend long weekends slaving over fixes for things that should never have been broken in the first place, or that we should have known about before they became a big problem.

In this article, plus the associated whitepaper (PDF), I uncover six common but scary surprises that often lurk behind the innocent-looking green "Play" signal in SQL Server Management Studio's Object Explorer. I'll also discuss how, with the right tools, you can uncover some of these potential threats, and make sure that the second another rears its head, you know about it. At the end of the PDF download, I provide a Further Reading list, where you can find deeper details on each of the specific "monsters", should you suspect that one of them has infested your SQL Servers.

This article, Lesson 3 of the Top 5 Hard-earned Lessons of a DBA, features just one my scary surprises, "When a TempDB Starts Dragging a Leg". To read the full Six Scary SQL Surprises, download the associated free whitepaper featured on this page and learn the lessons the easy way.

When a TempDB Starts Dragging a Leg

The TempDB database is available to all users of a SQL Server instance to house temporary objects such as cursors and tables, and it is where SQL Server creates various internal objects for sorting and spooling operations, as well as index modifications. It can get busy in there, especially if there are unruly processes. Of course, as developers and DBAs we must do all we can to mitigate potential problems by shying away from complex and unwieldy routines that overuse TempDB. However, we must also look out for configuration problems that can cause TempDB to start "dragging a leg".

Common causes of TempDB problems, in terms of configuration, include:

  • Only one TempDB data file
  • Too many TempDB data files (like one per core on an 80-core system)
  • Multiple TempDB data files, but with uneven sizes or uneven auto-growths

In order to understand why TempDB contention issues might arise, if we have too few TempDB files, let's go back to storage basics for a minute. SQL Server stores data, in disk or in memory, in 8KB pages, usually stored in groups of 8 KB pages (an extent).

Group of 8KB pages

Usually, SQL Server stores one object per extent but not always. If we're creating and dropping many small TempDB objects (for example, temporary tables, table variables, cursors), we will end up with mixed extents, where data for several objects is stored in the same extent.

There is only one page, the Shared Global Allocation Map (SGAM), which tracks space usage in extents for each 4GB of space. The following figure represents a single TempDB data file, with the black shaded blocks representing the mixed extents.

Shared Global Allocation Map

Every time we drop, create or expand an object in TempDB, SQL Server first has to access this single SGAM page. It then scans the Page Free Space (PFS) page to find out which mixed page it can allocate. After allocation, SQL Server must update the PFS and SGAM pages appropriately. If we have many mixed extent allocations, it can cause massive contention on the initial, single SGAM page.

As you can imagine, this is the reason why we might need to add more TempDB files. If we have four files for TempDB, then we have 4 SGAMs tracking extent usage and, assuming each file is evenly used, we spread the load and ease contention.

However, this only really works if we make sure to keep the TempDB files all the same size, meaning setting the same initial size for each file, and the same auto-growth increment. If we don't, and we end up with one TempDB file that is bigger than all the others, then we'll return to a situation of SGAM contention, as most of the action will hit the largest file.

Too many TempDB files
You may read advice online stating that there should be one TempDB file per processor core. Be very careful with this advice. If you have a lot of large temporary tables, or perform a lot of operations that spill to TempDB (for example, big sort operations) then it's possible that the overhead of SQL Server allocating pages among all the TempDB files, in a round-robin fashion, will slow down these operations. See Paul Randal's blog post for a few more details.

The key to taming TempDB contention is to use the right number of data files and keep them equally sized. Start with four TempDB data files, all equally sized and with the same auto-grow increment, and then constantly monitor TempDB activity levels, and look out for contention.

Trace Flag 1118
This trace flag "forces uniform extent allocations instead of mixed page allocations" and most people advise to enable it, in order to combat TempDB contention. As always, however, this advice can and likely will change with successive SQL Server version and editions.

After that, we need to monitor for contention issues, for example by tracking PAGELATCH waits on TempDB (see the Further Reading section in the download). If you're using a tool such as SQL Monitor then you can deploy the TempDB allocation contention custom metric which will alert you if you're running into SGAM contention.

"...monitor for contention issues, for example by tracking PAGELATCH waits on TempDB..."

SQL Monitor

Monitor TempDB allocation contention in real time, with SQL Monitor.

We have to keep watching TempDB constantly. Applications change, and may suddenly start putting more pressure on TempDB. SQL Server changes; many of its new features tend to use TempDB (for example, AlwaysOn Availability Groups store statistics and versions in TempDB), so we constantly need more throughput.


TempDB dragging a leg is just one of many scary surprises that may lurk behind the innocent facade of SSMS…in the whitepaper (PDF) I cover five more:

  • The monster that messes with your indexes
  • The cannibal that eats all your memory
  • The Solid State Drive that turns into a Zombie
  • The case of the horribly bloated tables
  • The call that's coming from inside the house…

Get the free whitepaper to find out more about these SQL spine-tinglers.


Whitepaper: 6 Scary SQL Surprises

by Brent Ozar

This article covers just one of Brent's Six Scary SQL Surprises. Find the other five in this free whitepaper.