kevin.kembel (1/2/2013)
So in your article where you showed the data persisting to disk when there was no memory pressure, that was because it was convenient for SQL at the time to be proactive?
Probably. All I was proving there was that table variables are not memory only, nothing more.
If the test was more intensive, and you were able to take a snapshot of the tempdb.mdf in the middle of it, are you suggesting that the data may not have been persisted to disk at that time, and existed only in memory?
Maybe. Maybe not. Could be that the very first page changed got written immediately to disk. Why don't you test and tell us?
If so, do you have any thoughts then on my original questions of using RAMDisk for tempdb storage, assuming that I'm not starving SQL Server of any RAM to do it?
Personally I wouldn't use a RAMDisk. I like my memory to be memory, for SQL to use for user database data cache, tempDB data cache, plan cache, log buffer, lock memory, object cache, memory grants, thread stacks, backup buffers and all of the other things in SQL that will use memory.
TempDB should not be the majority user of memory. If it is, I'd argue that there needs to be some serious tuning to get the TempDB usage under control.
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