How is memory and the temp db used in SQL?

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?

more ▼

asked Feb 02, 2011 at 01:47 PM in Default

Mark gravatar image

2.6k 23 25 27

(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

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.

create table #t (id int identity(1,1) primary key, f1 varchar(100))
declare @t table (id int identity(1,1) primary key, f1 varchar(100))
insert into @t select 'hej'
insert into #t select 'hej'
begin tran
insert into @t select 'hå'
insert into #t select 'hå'
select * from @t
select * from #t
more ▼

answered Feb 03, 2011 at 12:38 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

+1 for the transaction participation example
Feb 03, 2011 at 12:50 AM WilliamD
@Magnus, Terrific information and good links. Thank you sir!
Feb 03, 2011 at 08:21 AM Mark
(comments are locked)
10|1200 characters needed characters left

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][1]

You can modify his test code to show how allocations occur for a temp table of your design:

USE tempdb

-- Create a nice temp table
CREATE TABLE #MarksTempTable
    (c1 int IDENTITY,
     c2 int NOT NULL,
     c3 varchar(255)) ;

-- Find out the allocation information for the new temp table
-- Note that after initial creation, there has only been a meta data allocation
-- (Page numbers are all set to 0:0)
-- If you try reading the meta data using the DBCC commands IND and PAGE you 
-- won't get any results.
EXEC sp_AllocationMetadata '#MarksTempTable'

-- Now insert some data
INSERT INTO #MarksTempTable
        (c2, c3)
VALUES  (0,'')

-- Re-Run the meta data retrieval and there are now entries in the meta data
EXEC sp_AllocationMetadata '#MarksTempTable'

-- Now we can take a look at the information using DBCC
-- take the information from the column "First Page" of the meta data retireval

-- Redirect DBCC PAGE results to your screen instead of log file

-- Outputs a lot of information about the first page of your table
DBCC PAGE ('tempdb', <Number before colon>, <Number After Colon>, 3);     

-- Outputs information about pages in an index
DBCC IND ('tempdb','#MarksTempTable',-1) 

-- Turn the DBCC output redirection back off
[1]: http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-TF-1118.aspx
more ▼

answered Feb 03, 2011 at 12:49 AM

WilliamD gravatar image

25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left

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][1] by isolating tempdb files to separate disk, adequately size your tempdb, consider using multiple files for tempdb.

[1]: http://msdn.microsoft.com/en-us/library/ms175527.aspx
more ▼

answered Feb 02, 2011 at 08:20 PM

Cyborg gravatar image

10.6k 36 40 45

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: Feb 02, 2011 at 01:47 PM

Seen: 2140 times

Last Updated: Feb 02, 2011 at 01:47 PM