When to Use Tempdb in RAM

The temporary tempdb database is used for work space in sorting and for creating temporary tables in some join operations. Microsoft SQL Server provides a powerful option, tempdb in RAM, which allows the tempdb 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 that would otherwise be used for the SQL Server cache buffer system, and this can hurt performance.

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 versus 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 set by the memory option, and the server must be configured appropriately.

For example, if you use 10MB for tempdb in RAM, the 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. If this is a concern, it is better to leave tempdb as part of the regular data cache, which will have the effect of keeping the most active pages in RAM.

Using available RAM for the SQL cache buffer system is usually better than using a large chunk of it for tempdb in RAM. However, using tempdb in RAM might be beneficial if all of the following conditions are true:

• 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.
• Your applications have a locality of reference such that the SQL Server cache hit ratio is poor, even with a lot of available buffer cache. This hit ratio can be viewed by using SQL Performance Monitor to monitor the Cache Hit Ratio counter in the SQLServer object. For information about using SQL Performance Monitor, see Chapter 19, Monitoring Server Activity and Performance.
• 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.

  Or you can monitor tempdb space consumption by issuing this (or a similar) query either interactively or from a looping batch file:

  SELECT SUM(DPAGES) FROM TEMPDB..SYSINDEXES

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

If the decision is made to use tempdb in RAM, it is best to objectively verify the performance benefit obtained from this. To do this:

1. Select a query or small set of queries that typify your most frequently performed tempdb-intensive operations.
2. Run these several times, noting the execution time.
3. Reconfigure for tempdb in RAM, run the identical queries, and note the difference.

If the amount of improvement is not worthwhile, it is probably best to give the RAM back to the SQL cache system. Using 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.

The tempdb in RAM option 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.