In this tutorial, I will explain optimize tempdb performance. Tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down.

But before we start, there are things you should know :

  • If your SQL Server instance doesn’t employ many of the activities just described, then tempdb performance may not be an issue for you.
  • On the other hand, if your SQL Server instance uses many of these features, then tempdb could become a significant bottleneck for your SQL Server instance.
  • Only by investigation will you know.
  • Keep in mind that there is only one tempdb, and it is possible for one misbehaved application and database to indirectly affect the performance of all the other databaseson the same instance.

Tempdb Internals

  • Tempdb is dropped and recreated every time the SQL Server service is stopped and restarted.
  • When SQL Server is restarted, tempdb inherits many of the characteristics of model, and creates an MDF file of 8MB and an LDF file of 1MB.
  • Autogrowth is set to grow by 10% with unrestricted growth.
  • Each SQL Server instance may have only one tempdb, although tempdb may have multiple physical files.
  • Tempdb often doesn’t act like other databases
  • Tempdb only uses the simple recovery model.
  • Many database options can’t be changed (e.g. Database Read-Only, Auto Close, Auto Shrink).
  • Tempdb may not be dropped, detached, or attached.
  • Tempdb may not be backed up, restored, be mirrored, have database snapshots made of it, or have many DBCC commands run against it.
  • Tempdb logging works differently from regular logging. Operations are only minimally logged with enough information to roll back transactions, but not to be rolled forward. The log is truncated constantly, although it can grow with long-running transactions.

Execution Plans and tempdb

  • When a query execution plan is cached, the tempdb work tables required by the plan, if any, are often cached.
  • When a work table is cached, the table is truncated (from the previous execution of the code) and up to nine pages remain in the cache for reuse.
  • This improves the performance of the next execution of the query.
  • If the system is low on memory, the Database Engine removes the execution plan and drops the associated work tables.

Types of tempdb Problems

Generally, there are three major problems you run into with tempdb:

  • Tempdb is experiencing an I/O bottleneck, hurting server performance.
  • Tempdb is experiencing DDL and/or allocation contention on various global allocation structures (metadata pages) as temporary objects are being created, populated, and dropped. E.G. Any space-changing operation (such as INSERT) acquires a latch on PFS, SGAM or GAM pages to update space allocation metadata. A large number of such operations can cause excessive waits while latches are acquired, creating a bottleneck, and hurting performance.
  • Tempdb has run out of space.

Ideally, you should be monitoring all these on a proactive basis.

Identifying tempdb I/O Problems

  • Use Performance Monitor to determine how busy the disk is where your tempdb MDF and LDF files are located.
  • LogicalDisk Object: Avg. Disk Sec/Read: The average time, in seconds, of a read of data from disk. Numbers below are a general guide only and may not apply to your hardware configuration.
  • Less than 10 milliseconds (ms) = very good
  • Between 10-20 ms = okay
  • Between 20-50 ms = slow, needs attention
  • Greater than 50 ms = serious IO bottleneck
  • LogicalDisk Object: Avg. Disk Sec/Write: The average time, in seconds, of a write of data to the disk. See above guidelines.
  • LogicalDisk: %Disk Time: The percentage of elapsed time that the selected disk drive is busy servicing read or write requests. A general guideline is that if this value > 50%, there is an I/O bottleneck.
  • SQL Server Database: Log Bytes Flushed/sec: The total number of log bytes flushed. A large value indicates heavy log activity in tempdb.
  • SQL Server Database: Log Flush Waits/sec: The number of commits that are waiting on log flush. Although transactions do not wait for the log to be flushed in tempdb, a high number in this performance counter indicates an I/O bottleneck on the disk associated with the log.

Identifying Contention on Allocation Structures

Use these performance counters to monitor allocation/deallocation contention in SQL Server:

  • Access Methods:Worktables Created/sec: The number of work tables created per second. Work tables are temporary objects and are used to store results for query spool, LOB variables, and cursors. This number should generally be less than 200, but can vary based on your hardware.
  • Access Methods:Workfiles Created/sec: The number of work files created per second. Work files are similar to work tables but are created by hashing operations. Work files are used to store temporary results for hash joins and hash aggregates.
  • Temp Tables Creation Rate: The number of temporary tables or variables created/sec.
  • Temp Tables For Destruction: The number of temporary tables or variables waiting to be destroyed by the cleanup system thread.

Add More RAM to Your Server

  • Depending on the operation, SQL Server tries to perform the action in the buffer cache. (e.g. sorts, CTEs)
  • If the buffer cache does not have enough available space, then the operation may have to spill to tempdb.
  • This places additional overhead on tempdb.
  • If your server is experiencing a memory bottleneck, then adding RAM can help reduce the load on tempdb.
  • On the other hand, if your server has plenty of memory, adding more won’t help tempdb performance.

How to Pre-allocate tempdb Space

Use ALTER DATABASE, or SSMS, to increase the size of the tempdb database MDF and LDF files.

    USE master;
    GO
    ALTER DATABASE tempdb
    MODIFY FILE
    (NAME = tempdev,
    SIZE = 20MB);
    GO
    ALTER DATABASE tempdb MODIFY FILE
    (NAME = templog, SIZE = 10MB)
    GO

Locate tempdb on Fast I/O Subsystem

  • Always locate tempdb on the fastest I/O subsystem you have available.
  • Prefer RAID 1 or RAID 10. RAID 5 is slow for writes and should generally be avoided for tempdb, as tempdb is often write-intensive.
  • If using a SAN, consult with a SAN engineer to ensure that tempdb won’t be affected by other disk I/O.
  • Consider SSD drives for tempdb MDF and LDF files.

How to Move tempdb

  • Determine the current location of the MDF and LDF files
  • Run the ALTER DATABASE command to move the files

Avoid Using TDE

  • SQL Server 2008 offers a new database-level encryption feature called Transparent Database Encryption (TDE).
  • If this is turned on for one or more databases on a SQL Server instance, then all the activity in tempdb (whether it comes from a encrypted or non-encrypted database) will be encrypted.
  • Encryption increases CPU usage and slows down tempdb performance.
  • If you decide to use TDE, you will want to incorporate as many of the tempdb performance tuning tips that I have suggested in order to help overcome the additional burden added by TDE.


HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu revolutionized hosting with Plesk Control Panel, a Web-based interface that provides customers with 24x7 access to their server and site configuration tools. Plesk completes requests in seconds. It is included free with each hosting account. Renowned for its comprehensive functionality - beyond other hosting control panels - and ease of use, Plesk Control Panel is available only to HostForLIFE's customers. They offer a highly redundant, carrier-class architecture, designed around the needs of shared hosting customers.