Let's say that I'm about to run a query's results into a temp table and there is plenty of memory free for the table's values. It's an ideal scenario: That query is the only extra process running and the server doesn't have any more users logged on or other applications running at the time.
What happens when it is run? Is it always created in the temp db even when there is plenty of memory to store it in memory alone? What if the table's tiny - say, less than one MB?
I'm also interested in the same questions for table variables. It works the same?
asked Feb 02 '11 at 01:47 PM in Default
Though this blogpost does not totally answer your question, it does present lots of information about temp tables and table variables.
In this FAQ (question 4) about SQL Server 2000, it's said that both temp tables and table variables are stored in memory if there's enough memory.
This MSDN Social discussion gives a somewhat clearer picture to what's meant by the answer to Q4 in the above FAQ.
And so on. There are tons of discussions about how #temp tables are created and stored. There's no consencus about where #temp tables are stored, but the majority of I-know-SQL-Server-people seems to argue that they are stored in memory only if possible.
Finally, consider this when you use #temp tables and @table variables.
answered Feb 03 '11 at 12:38 AM
tempdb works just like any other database with regards to caching and writing to disk. If the table is small enough to fit into cache it will.
To find ou how the table is created you need to start playing around with trace flags and DBCC commands.
Whilst the topic isn't directly related to memory usage and tempdb, this blog post by Paul Randal dives into table structures in tempdb: [Misconceptions around TF 1118]
You can modify his test code to show how allocations occur for a temp table of your design:
answered Feb 03 '11 at 12:49 AM
Its a common mis-consumption that table variables always reside on memory. If you have plenty with cache then, temp tables or variable tables will reside in cache only. If cache is low then tempDB will expand to disk. Consider [optimizing the tempDB] by isolating tempdb files to separate disk, adequately size your tempdb, consider using multiple files for tempdb.: http://msdn.microsoft.com/en-us/library/ms175527.aspx
answered Feb 02 '11 at 08:20 PM