question

dataminor avatar image
dataminor 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?
tempdbmemorytemporary-tables
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO avatar image GPO commented ·
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).
2 Likes 2 ·
Gazz avatar image Gazz commented ·
I don't mean to be rude, but why does it matter?
1 Like 1 ·
Gazz avatar image Gazz commented ·
Thanks, I will check the video out
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
0 Likes 0 ·
happycat59 avatar image
happycat59 answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image David Wimbush commented ·
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.)
4 Likes 4 ·
GPO avatar image
GPO answered
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.