Microsoft SQL Server provides a powerful feature called "tempdbin RAM." This allows the temporary database tempdb, which is used for workspace in sorting and for creating temporary tables in some join operations,to be made entirely memory resident. In some specific situations, this canprovide a performance advantage. However if tempdb in ram is usedinappropriately, it can consume memory which would otherwise be used forthe 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 thanas the location of tempdb. Data in tempdb will itself be cached using theSQL cache buffer system's LRU algorithm.
This is analogous to the decision of using a RAM disk vs. using thesmartdrive cache program on a Microsoft Windows workstation. In this case,the RAM consumed for the RAM disk is unavailable for smartdrive, andcan only be used for objects specifically placed on the RAM disk. In afew cases where your knowledge of the application environment is suchthat you know that most access is to a few files, and if they are smallenough to fit on the RAM disk, and if your remaining disk accesses havesuch poor reference locality that no feasible amount of cache willprovide a good hit ratio, then a RAM Disk might be superior tosmartdrive. However in most cases smartdrive will be superior, since itcaches all accesses (not just those placed on the RAM disk).
Similarly, use of tempdb in RAM can accelerate tempdb operations but willdeplete memory available for the SQL cache buffer, which can lower thecache hit ratio. Memory used for tempdb in RAM is allocated separately fromthe pool seen in sp_configure "memory", and the server must be configuredappropriately. For example if you use 10MB for tempdb in RAM, the SQL NTsp_configure "memory" setting must be reduced by 10MB to free up memory forthis. By contrast giving all available memory to SQL Server (as opposed tosetting some aside for tempdb in RAM) can increase the cache hit ratio. TheSQL cache buffer system will cache all disk I/O operations, includingtempdb.
Because of the limited amount of RAM available on many machines, this willconstrain the available size of tempdb when used in RAM. If unforeseengrowth requirements for tempdb materialize, this could be a problem. Itdoes no good to have tempdb partially in RAM and partially on disk. It alsodoes no good to exceed the available amount of physical memory when usingtempdb in RAM. Even if this worked, tempdb references would be simply pagedto disk, eliminating any possible benefit. See the SQL NT ConfigurationGuide for configuring tempdb in RAM.
If using available RAM for the SQL cache buffer system is usually betterthan using a large chunk of it for tempdb in RAM, are there ever any caseswhere this is not true? Yes, if all of the following conditions are true,using tempdb in RAM might be beneficial:
- 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 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."
- 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:
either interactively or from a looping batch file to monitor tempdb space consumption.
SELECT SUM(DPAGES) FROM TEMPDB..SYSINDEXES
- The tempdb operations are sized such that they will fit on the tempdb made possible by your RAM configuration.
If the decision is made to place tempdb in RAM, it is best to objectivelyverify the performance benefit obtained from this. Select a query or smallset of queries which typify your most frequently-performed tempdb-intensiveoperations. Run these several times, noting the execution time. Thenreconfigure for tempdb in RAM, run the identical queries and note thedifference. If the amount of improvement isn't worthwhile, it is probablybest to give the RAM back to the SQL cache system.
Placing tempdb in RAM is safe and will not harm database integrity orrecoverability. This is because tempdb is only used for intermediateoperations, and is re-created from scratch upon each server restart.
Tempdb in RAM is an important performance tool that is available for caseswhere analysis shows it to be beneficial. In some cases it can provide asignificant performance improvement, but it should not be usedindiscriminately.
For more information on SQL performance issues, see article 110352
,"Optimizing SQL Server Performance." For more information on configuringSQL NT memory, see article 110983
, "Recommended SQL Server for NT MemoryConfigurations."