Blog | Feb 26, 2014

Knowing Tempdb – Bringing the Best Out Of Tempdb

There are many misconceptions & myths about tempdb and the purported best practices are inconsistent at best. The purpose of this blog is to help make the right decisions for managing tempdb and to determine the best solution for a given scenario.

In order to gain a better understanding, we will look at what tempdb is, how it is used and some of the major common problems as well as methods to prevent them. At the end we will provide some best practices on configuring and monitoring Tempdb.

What is Tempdb?
The Tempdb system database is similar to other user databases except that it doesn’t persist after SQL server shuts down; which implies that with every restart the tempdb gets recreated and is copied from a model database.

A few more points on Tempdb:

• Only one file group is allowed for data and one file group for logs.
• Auto shrink is not allowed for tempdb.
• The database CHECKSUM option cannot be enabled.
• A database snapshot cannot be created on tempdb.

What uses Tempdb?

• Temporary user objects

  1. Temp tables
  2. Table variables.
  3. Temp procedures.
  4. Global Temp tables.
  5. Cursors

• Work tables/ work files / intermediate results

  1. Sorts

• Index Rebuilds

• Some group by, order by or union operations:

  1. Spools
  2. Hash joins and hash aggregates.
  3. Spills to tempdb.
  4. Temporary LOB storage.
  5. Common table expressions(CTE)

• Version store for data modification transactions from:

  1.  Read committed snapshot isolation.
  2. Snapshot isolation.
  3. Online Indexes.
  4. After triggers.
  5. Multiple Active Results Set (MARS)

Tempdb space usage:

The following 3 types of objects can occupy Tempdb:

1. Internal Objects: Created internally by SQL server.
2. Version Stores: Used to store row versions generated by transactions for features such as snapshot isolation, triggers, MARS and Online index rebuild.
3. User Objects: Includes both user defined tables and indexes, system catalog tables and indexes.

Now, let’s understand what tempdb is used for followed by performance issues and ways to prevent them.

Tempdb contention:

One of the major performance problems with tempdb is contention on allocation pages. There are three types of pages that experience this issue:

Ways to prevent the above problem:
Starting with a proper configuration of tempdb is a must. Plus follow the recommendations as given below:

1. Data File per CPU ratio:
Microsoft recommends one data file per logical CPU (1:1 ratio).

2. Pre- size files to avoid auto-growth:
The number of data files for tempdb is only one best practice for tempdb that affects tempdb contention. It is equally important that the data files are of the same size and maintained at the same size. The data files should be pre-sized to avoid any auto-growth.

3. Disk Performance and RAM:
Put the tempdb on the fastest disk possible to avoid any disk IO issues. Secondly, help tempdb maintain objects in RAM by giving it plenty of RAM to use.

4. Version Store monitoring:
Version store creates issues if the version cleanup rate is slower than the version growth rate. The SQL will only clean up versions that are older than the oldest active transaction in the version store. A long running transaction can easily cause the version store to grow very large because unused versions must be maintained as long as an older transaction than itself exists.

Tempdb is a critical component of SQL Server and can be a major performance pain point if not managed properly. To ensure that tempdb performs well, proper configuration along with a consistent base lining and monitoring of tempdb is required.

A few best practices:
• Multiple data files pre-sized equally to avoid auto-growth.
• Use one data file per CPU.
• Provide plenty of RAM for tempdb to use.
• Make sure queries are optimized to avoid tempdb spills.
• Monitor for tempdb contention.
• Monitor the version store for long running transactions.
• Disable auto growth for data files.
• Set auto growth for log file to fix value like 512 MB.

When the above best practices are followed performance is improved, user queries run faster, there is a low latch contention and many common problems can be avoided.