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?

more ▼

asked Jan 15, 2016 at 12:12 AM in Default

avatar image

310 10 9 16

I don't mean to be rude, but why does it matter?

Jan 15, 2016 at 09:02 AM Gazz

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).

Jan 15, 2016 at 07:44 PM GPO

Thanks, I will check the video out

Jan 18, 2016 at 09:05 AM Gazz

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.

Jan 18, 2016 at 09:36 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Jan 15, 2016 at 04:08 AM

avatar image

360 2 1

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.)

Jan 15, 2016 at 09:08 AM David Wimbush
(comments are locked)
10|1200 characters needed characters left

Here's Kendra's video. Hope it's useful.

more ▼

answered Jan 15, 2016 at 07:45 PM

avatar image

4.9k 41 51 58

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 15, 2016 at 12:12 AM

Seen: 733 times

Last Updated: Jan 18, 2016 at 09:36 AM

Copyright 2017 Redgate Software. Privacy Policy