INF: When to Use Tempdb In RAM

  - Microsoft SQL Server version 4.2, 6.0 and 6.5

SUMMARY

Microsoft SQL Server provides a powerful feature called "tempdb in RAM." This allows the temporary database tempdb, which is used for work space in sorting and for creating temporary tables in some join operations, to be made entirely memory resident. In some specific situations, this can provide a performance advantage. However if tempdb in ram is used inappropriately, it can consume memory which would otherwise be used for the SQL Server cache buffer system, and this can hurt performance.

This article discusses when and when not to use tempdb in RAM.

MORE INFORMATION

In most cases, the available RAM is best used as a data cache, rather than as the location of tempdb. Data in tempdb will itself be cached using the SQL cache buffer system's LRU algorithm.

This is analogous to the decision of using a RAM disk vs. using the smartdrive cache program on a Microsoft Windows workstation. In this case, the RAM consumed for the RAM disk is unavailable for smartdrive, and can only be used for objects specifically placed on the RAM disk. In a few cases where your knowledge of the application environment is such that you know that most access is to a few files, and if they are small enough to fit on the RAM disk, and if your remaining disk accesses have such poor reference locality that no feasible amount of cache will provide a good hit ratio, then a RAM Disk might be superior to smartdrive. However in most cases smartdrive will be superior, since it caches all accesses (not just those placed on the RAM disk).

Similarly, use of tempdb in RAM can accelerate tempdb operations but will deplete memory available for the SQL cache buffer, which can lower the cache hit ratio. Memory used for tempdb in RAM is allocated separately from the pool seen in sp_configure "memory", and the server must be configured appropriately. For example if you use 10MB for tempdb in RAM, the SQL NT sp_configure "memory" setting must be reduced by 10MB to free up memory for this. By contrast giving all available memory to SQL Server (as opposed to setting some aside for tempdb in RAM) can increase the cache hit ratio. The SQL cache buffer system will cache all disk I/O operations, including tempdb.

Because of the limited amount of RAM available on many machines, this will constrain the available size of tempdb when used in RAM. If unforeseen growth requirements for tempdb materialize, this could be a problem. It does no good to have tempdb partially in RAM and partially on disk. It also does no good to exceed the available amount of physical memory when using tempdb in RAM. Even if this worked, tempdb references would be simply paged to disk, eliminating any possible benefit. See the SQL NT Configuration Guide for configuring tempdb in RAM.

If using available RAM for the SQL cache buffer system is usually better than using a large chunk of it for tempdb in RAM, are there ever any cases where this is not true? Yes, if all of the following conditions are true, using tempdb in RAM might be beneficial:

1. You have a significant amount of available system RAM. This normally would equate to more than 64 MB, with amounts of 128 MB and above more typical.

2. Your applications have a locality of reference such that the SQL NT cache hit ratio is poor, even with a lot of available buffer cache. This hit ratio can be monitored with Performance Monitor as the object "SQLServer", and the counter "Cache Hit Ratio."

3. Your applications do a lot of tempdb operations. Rather than guess whether this is the case, this can be monitored by using sp_lock to observe the lock activity in tempdb while queries are running. Also, you can do this or a similar query:

   SELECT SUM(DPAGES) FROM TEMPDB..SYSINDEXES

   either interactively or from a looping batch file to monitor tempdb space consumption.

4. The tempdb operations are sized such that they will fit on the tempdb made possible by your RAM configuration.

Placing tempdb in RAM is safe and will not harm database integrity or recoverability. This is because tempdb is only used for intermediate operations, and is re-created from scratch upon each server restart.

Tempdb in RAM is an important performance tool that is available for cases where analysis shows it to be beneficial. In some cases it can provide a significant performance improvement, but it should not be used indiscriminately.

For more information on SQL performance issues, see article Q110352, "Optimizing SQL Server Performance." For more information on configuring SQL NT memory, see article Q110983, "Recommended SQL Server for NT Memory Configurations."