asked
Does my temp table necessarily write out to tempdb on disk?
Say I create a small temp table (say 10,000 rows and 5 int columns for example): SELECT [blah] INTO #temp FROM [blah] WHERE [blah] Say I also have plenty of free memory. Is it possible that the table could spend it's life in memory and never need to be written out to disk? If there are some circumstances where temp tables don't need to be written to disk, and some circumstances where they do, how can I predict with useful accuracy, whether it (the writing to disk) is going to happen?
Fair question @Gazz :-) It matters because doing your work in memory is waaaay faster than writing out to disk. If all temp tables were written to disk regardless of their size, then you'd look for alternatives like table variables. Kendra Little has a good video here about this sort of thing (I'll post it below as an answer because I can't post a link as a comment).
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
I think that there is no simple way to predict whether the data in a temporary table will ever actually be written to disk. If there is enough memory available, it will probably not ever get written to disk. As memory pressure grows, it is more likely that the temp table will end up on disk. SQL Server does try to avoid disk activity where it can since this can be relatively expensive. Yes most data does normally end up on disk if it is important enough but temporary tables contain temporary data - it may well be wasted effort writing it to disk.
That's how I understand it too. Temporary objects start out in memory and are only 'spilled' to disk when there is memory pressure. It's not predictable except in no-brainer cases like: 1) small number of rows will probably never make it to disk and 2) a large amount of data will probably always be written to disk. What exactly does large and small mean? You guessed it: it depends! (On how much RAM is available at the time.)
Here's [Kendra's video][1]. Hope it's useful. [1]:
http://www.brentozar.com/archive/2014/06/temp-tables-table-variables-memory-optimized-table-variables/
21 People are following this question.
