kevin.kembel (12/21/2012)
GSquared (12/20/2012)
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'd heard temporary tables, unlike variables, were always stored in TempDb's data files and not in RAM - do you have any links or articles that explain this that I could read? From what I can find it looks like we're both wrong, it looks like both table variables and temporary tables are always stored in TempDb and not in memory.
http://cybersql.blogspot.ca/2009/12/yes-table-variables-and-temp-tables.html
We've noticed in our live environment that the most active database for disk i/o in our system is TempDb, it actually noses out our OLTP. This makes sense if the article I linked to is true, and temporary tables and table variables are always written to the TempDb data files. We didn't think this was because of a lack of RAM (currently 32GB for our 35GB OLTP database), but I guess we'll know when we move to the 64GB system and monitor TempDb usage there.
I wrote a well-received article here on SSC a few years back that covers the differences: Comparing Table Variables to Temporary Tables[/url]
As far as memory or tempdb, check out this from http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k :
A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).
Summarize: if the data will fit in ram, it might not be written out to disk. But it always has to be able to be written out to disk, since the data might be greater than the available memory to hold the data. If it's written out to disk, it's written out to the tempdb database.
Don't forget that if your queries end up creating worktables, those are in tempdb. Your high IO from tempdb could just be from poorly performing queries or execution plans that were generated based upon poor statistics (don't forget that table variables don't have any statistics, so if you're using them you just might see some pretty remarkable IO improvements by changing to temp tables).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes