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.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.