ScottPletcher (2/21/2013)
Alexander Suprun (2/21/2013)
ScottPletcher (2/21/2013)
RAM is always used first to hold temp tables and table variables. But, if (and only if) RAM available for holding such rows is exceeded, then rows are physically stored in tempdb as necessary.GilaMonster (2/21/2013)
They, however, may not be on disk. Allocated space in TempDB != written to the disk. They won't necessarily get written to disk unless there's memory pressure.So if I have tempdb data/log files 1MB each, available RAM 1GB and I create 10MB temp table then my tempdb file is still gonna be 1MB? Something tells me it's totally not true.
It depends. SQL won't necessary use all available memory just for your request. But if RAM is available for your task, up to its valid limit, SQL won't write it to disk. It makes sense really. Processing solely in memory is always vastly faster than going to disk.
I've asked a specific question: 10MB of memory is required for tempdb object and 1Gb is available, is it going to write to disk or not? What is the "valid limit"? If this "valid limit" is 1MB then there is not even a point of mentioning this feature because it would be completely useless in most cases.
Even if something makes sense it doesn't necessarily mean that it's implemented this way.