We have a SQL Server 2012 Enterprise setup with a large amount of RAM (256 GB), and at this time fairly small databases (about 17GB). Right now we are experiencing performance issues due to various coding issues which will hopefully be resolved by some code reviews and optimization. At the same time there is a movement to try to improve the performance by getting faster disk for the tempDB LUNs, like using a FusionIO PCIe flash card.
My concern is that given that SQL Server still has plenty of RAM to take advantage of, that we won't see any real performance improvement from adding faster disk. I figure that most of the tempDB operations would take place in RAM, or take place on disk but end up cached in RAM (either on the host, HBA, or SAN).
Does anyone know if SQL Server 2012 will still end up waiting for IO blocking on tempDB disk operations when it has plenty of RAM to use, or is the tempDB disk activity we are seeing just there to provide persistence or durability?
Best Answer
Even if you have plenty of DRAM,
tempdbmay still be used.This happens in a few situations:
Snapshot isolation: Using this feature can create a lot of tempdb activity.
Hash and sort Spills: When the optimiser creates a query plan, it will try to estimate the total amount of memory it needs to run the query. Before the query runs, the estimated memory is allocated. If it turns out (for example, due to bad statistics) that the allocated memory is too small, then SQL Server will spill operators like hash tables and sort buffers to
tempdb, EVEN if there is enough memory after the query has started to satisfy the requirement.Or course, if your queries are perfectly tuned and you run without snapshot isolation, you can avoid these effects. But in most situations, some amount of
tempdbactivity is to be expected, even on a large DRAM system.