Tips on TempDB

Are you curious about TempDB? Allow me to help make your understanding of the subject permanent. TempDB is an interesting system database that doesn’t behave quite like the others. It’s kind of like a quirky friend that you’ve come to rely on when you need a hand with getting something done.

Who is TempDB for?

TempDB is accessible to all users that are connected to an instance of SQL Server.

What is TempDB?

TempDB is a system database that’s used to store temporary objects. It utilizes minimal logging, meaning it only stores the information required to recover a transaction – this means no point-in-time recovery. You can’t perform a backup or restore on TempDB, and each time SQL Server is restarted, TempDB gets re-created with the last configured settings.

How does TempDB get used?

TempDB stores a number of different temporary objects:

• User-created temporary objects: Such as global (prefixed by ##) or local (prefixed by #) temporary tables, temporary stored procedures and cursors.
• Internal temporary objects: Like work tables for intermediate results (any sorting, such as GROUP BY or ORDER BY)
• Version stores: Row versions for data-modification transactions when certain isolation levels are in use.
• …And certain features like online index operations

Think about it like this: I’m 5’7” and 150 pounds. Would you rather have me or Dwayne “The Rock” Johnson helping you move your furniture? If it’s just a chair or two, I’m happy to help, but if you want me to move a sofa and don’t plan on doing any heavy-lifting yourself, you’ll probably want to get a bigger guy.

Just like when you’re moving furniture, when using TempDB, the size configurations are very important.

Common Issues

TempDB typically has three common issues that a DBA can run into: I/O bottlenecks, low disk space, and page contention. These issues and their resolutions are often interrelated.

If you really want my help, and I see that I’m not going to be big enough to move your sofa, I’ll try my hardest to bulk up so that I can perform the task. TempDB feels the same way, and will automatically grow in size (the default autogrowth setting is by 10% of its current size) in order to accomplish whatever job it set out to do. It’s great that TempDB is so willing to help, but in some cases, it can become so concerned with getting bigger that it causes I/O pressure on the disk, and performance can suffer. In even worse scenarios, TempDB might decide that it needs to be so huge that it consumes the whole disk. How am I going to help you move your furniture if my muscles can’t even fit through the doorframe anymore?!

Since TempDB is so handy, everyone wants a piece of it, and TempDB will put a lot of work on its plate, trying to satisfy everyone’s demands.  This can cause page contention, which we witness in the form of the PAGELATCH wait type (Note: This is not PAGEIOLATCH) denoting that a page is protected and already in memory. Queries needing TempDB’s help will have to wait for their turn.

Solving Common Issues

So, how do we keep TempDB from feeling over-worked and checking into the Smack-Down Hotel? The answer lies in capacity planning.

The basic concepts of capacity planning for TempDB include:

• Set autogrowth to a pre-set amount: If TempDB gets to be the size of The Rock we don’t want it to automatically grow by a percentage of its current size each time. Set the number to a reasonable size to avoid the continuous need for growth, but keep it low enough to avoid wasted space.
• Capture and replay activity using a trace or run individual queries: Monitor TempDB’s growth as it is used.
• The tricky part: Estimate the work load (while accounting for concurrent user-activity)

You can use SYS.DM_DB_SESSION_SPACE_USAGE and SYS.DM_DB_TASK_SPACE_USAGE to identify queries which are consuming TempDB

• Configure the appropriate number of TempDB DATA files:  If you thought having one Dwayne “The Rock” Johnson helping you move furniture was cool, imagine having two of him! Increasing the number of TempDB data files will allocate work in a round-robin form.  This will relieve some of the pressure on TempDB. It’s like having one Dwayne “The Rock” Johnson Feng Shui your living room while another does the same to your kitchen. It is best practice to set the number of TempDB data files equal to the number of logical CPU cores. For example, if you have a dual-core processor, then set the number of TempDB data files equal to two.  If you have more than 8 cores, start with 8 files and add four at a time as needed.
• All TempDB files are created equal: This isn’t George Orwell’s Animal Farm. Ensure that the size and growth settings for all TempDB data files are configured in the same manner.
• Disk Placement: If possible, spread TempDB data files across different disks to reduce I/O contention. Putting TempDB data files on a different disk than the user data files will further reduce I/O contention. Be sure to use fast disks whenever possible.

Keep in mind, that TempDB configurations are largely environment-specific. You might not actually need to have the same number of TempDB files as your logical CPU cores. In fact, having too many TempDB data files can cause performance problems due to slowing down the round-robin style allocation. Use your judgement and knowledge of your environment to determine what’s right for your system. If it’s configured properly, then you and your TempDB can be tag-team wrestling (or moving furniture) with queries like heavyweight champions.

For some more information, check out the following links:

Microsoft Technet: tempdb Database

Microsoft Technet: Capacity Planning for tempdb

SQL Skills: Paul Randal’s A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core