Article ID: 115050 - View products that this article applies to.
This article was previously published under Q115050
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.
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:
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 110352
(http://support.microsoft.com/kb/110352/EN-US/ ), "Optimizing SQL Server Performance." For more information on configuring SQL NT memory, see article 110983
(http://support.microsoft.com/kb/110983/EN-US/ ), "Recommended SQL Server for NT Memory Configurations."
Article ID: 115050 - Last Review: February 22, 2005 - Revision: 3.1