GSquared (12/20/2012)
Most of what accesses tempdb will already be perfectly happy to live in RAM till it runs out and has to use the disk. So the gains are likely to be minor, if they exist at all. Table variables, temp tables, and worktables, only spill out of RAM onto drives, if they have to because of lack of available RAM, per MS.
I've look at Google and your links, and it still appears to me that what you said before isn't entirely correct - which is why I was hoping for some explicit clarification. The articles you just linked to are a perfect example of what I mean. They seem to contradict each other, as well as what you said about table variables and temp tables (quoted above). So far I'm still assuming that they do always write to disk - both temp tables and table variables because that's the bulk of what I've read as well as what I've experienced when I monitor my TempDB's MDF read/write usage. If RAM is available, they cache as per usual, but they will still always write to disk even if sufficient RAM is available.
From the first link, "A Trio of Table Variables" http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/%5B/b%5D%5Bhr%5D
This myth can be broken down into two parts:
1. ...
2. That table variables are not written to disk
To answer myth #2, it looks like he creates a basic table variable example that is definitely not under memory pressure...
20 Megabytes. The SQL instance I’m running this on is allowed to use up to 2 GB of memory. No way on earth is this table variable going to cause any form of memory pressure (and I promise there is nothing else running)
He then kills SQL, and uses a hex editor to view the tempdb.mdf data file on the disk. He shows the data in the disk to prove that his table variable was written to disk even when there was plenty of RAM available. Then says this about myth #2...
That pretty much speaks for itself. This myth, clearly false.
What he seems to be showing here directly contradicts what is said in the second article that you linked to from the same site:
From the second link, "Temp Table and Table Variables" http://sqlinthewild.co.za/index.php/2007/12/19/temp-tables-and-table-variables/%5B/b%5D%5Bhr%5D
... Are not persisted to disk unless there is memory pressure, or the table is too large to fit in the data cache
So the first article shows that table variables are written to disk even when there is no memory pressure, while the second article claims in a summary of table variables that they are not persisted to disk unless there is memory pressure. I really must be misunderstanding something fundamental, like you said there's lots of information on Google/Bing, but most of what I find seems to be obviously contradictory.
The only obvious difference in context between them that I see is that the second link claiming table variables "are not persisted to disk unless there is memory pressure" is over 5 years old, where the newer article shows that they are persisted to disk even when RAM is available. Does the newer article debunk the myth that they perpetuated in the older article?
I thank you for posting those two links, these are perfect examples to work with, can you explain them to me and how they are both correct?