• kevin.kembel (1/2/2013)


    The consensus by everyone so far in this thread definitely seems to be that table variables and temp tables work in RAM when available, and only spill to disk when RAM is not available.

    No, that's not so. The concensus is that that's what usually happens, not that it's what always happens.

    One posted said that although that shows space being allocated, data is never written to that space until it becomes necessary.

    I suspect that there you are referring to my earlier post - but that isn't what I said; what I said was that most often they won't be written to disc. However, even "most often" will be wrong too (unless there is most often adequate disc performance), because it depends on two things: (i)the pressure on the cache and (ii) the pressure on the discs. Usually if the pressure on the cache is low then the pressure on the discs will be low too so the sql system will sometimes decide to write a page now, while it costs effectively nothing, rather than later, when it may cost something. But optimising these writes that cost nothing by putting the data on SSD is a waste of resource - optimise a nothing cost and you still have a nothing cost, so no gain - and I think that's all you would be optimising, since you have your data and logs separated and presumably don't have non-db activity on the same drives as tempdb data.

    Tom