Alexander Suprun (2/21/2013)
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?
Maybe, maybe not. Depends what else is going on. I've done that (small table, lots of free memory) and had the temp table written to disk. Not written to disk and removed from memory, so access to that temp table was still fast and from memory, but the table was written to disk. Probably the lazy writer being pro-active.
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.
Indeed that's totally not true. The table would be allocated space in TempDB as I already explained, if there's not enough space in TempDB, the database would grow so that there would be space when/if the table needs to be written back, there's somewhere to write it.
Bear in mind that it's not memory or disk, it's entirely possible for a table to be in both, written back to disk, but still in the buffer pool. In that case access is still fast, as it's in memory.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability