asked
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?
Though [**this blogpost**][1] does not totally answer your question, it does present lots of information about temp tables and table variables. In **[this FAQ][2]** (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**][3] 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å' rollback select * from @t select * from #t [1]:
http://sqlnerd.blogspot.com/2005/09/temp-tables-vs-table-variables.html [2]:
http://support.microsoft.com/kb/305977/en-us [3]:
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/1016bf4b-3d42-4bcd-b9c9-d1680d1c076c/
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
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 GO -- Create a nice temp table CREATE TABLE #MarksTempTable (c1 int IDENTITY, c2 int NOT NULL, c3 varchar(255)) ; GO -- 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' GO -- Now insert some data INSERT INTO #MarksTempTable (c2, c3) VALUES (0,'') GO -- Re-Run the meta data retrieval and there are now entries in the meta data EXEC sp_AllocationMetadata '#MarksTempTable' GO -- 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 DBCC TRACEON (3604); GO -- Outputs a lot of information about the first page of your table DBCC PAGE ('tempdb', , , 3); -- Outputs information about pages in an index DBCC IND ('tempdb','#MarksTempTable',-1) GO -- Turn the DBCC output redirection back off DBCC TRACEOFF (3604) GO [1]:
http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-TF-1118.aspx
