are temp tables written to disk?

are temp tables written to disk? or are they cached like the database is
cached on queries.
Yes they are written to disk, although it's often for only a short
period of time. All database writes, including writes to tempdb which
is in effect handled just like normal user databases, in SQL Server are
written to memory first (making the page in memory "dirty") and then
flushed to disk when the lazy writer process gets around to it.
I guess, in theory, the temp table may be dropped before the lazy writer
writes that data to disk and so it might not ever make it to the disk
but I've never tried to analyse that scenario.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:

>are temp tables written to disk? or are they cached like the database is
>cached on queries.
>
>
|||I "heard" that since SQL Server 2000 caches the database, that temp tables are written to memory before disk? and thus there is no significant performance difference between temp table and table variables if you have lots of memory to begin with.
I really never heard of this, but I really think this person was just trying to cover their butt on their lack of knowledge as well as critical mistake
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl...
Yes they are written to disk, although it's often for only a short period of time. All database writes, including writes to tempdb which is in effect handled just like normal user databases, in SQL Server are written to memory first (making the page in memory "dirty") and then flushed to disk when the lazy writer process gets around to it.
I guess, in theory, the temp table may be dropped before the lazy writer writes that data to disk and so it might not ever make it to the disk but I've never tried to analyse that scenario.
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
are temp tables written to disk? or are they cached like the database is
cached on queries.
|||Temp tables vs table variables is a fairly common debate topic.
The person you're talking about is kind of right. Every database write
happens to memory first and so, in theory, query performance shouldn't
really be hindered by I/O to disk (as that is essentially done
asynchronously by a background spid). But this assumes you have no
great memory pressure.
IMHO there's really not enough difference, performance-wise, to be
terribly concerned about it. Temp tables are just like any other
permanent table (except that they are automatically dropped when they go
out of scope) and as such SQL Server maintains statistics on the columns
in those temp tables (SQL Server does *not* collect statistics on table
variables). As such the query optimiser can often come up with better
plans for temp tables (than it can with table variables) and manipulate
the data in them faster. However, table variables implement less
locking generally...so it can swing the other way too. The main reason
I would use a table variable would be if I wanted to return a result set
from a UDF, because you can only do that using a table variable. So
that would be a functionality reason more so than a performance reason.
Aaron Bertrand
<http://www.microsoft.com/communities...taSvcParams%5e>
(SQL MVP) has a great website called ASPFAQ <http://www.aspfaq.com> that
has heaps of different articles about different things people ask about
SQL Server. Here's what he says about temp tables vs table variables:
Should I use a #temp table or a @.table variable?
<http://www.aspfaq.com/show.asp?id=2475>
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
[vbcol=seagreen]
> I "heard" that since SQL Server 2000 caches the database, that temp
> tables are written to memory before disk? and thus there is no
> significant performance difference between temp table and table
> variables if you have lots of memory to begin with.
> I really never heard of this, but I really think this person was just
> trying to cover their butt on their lack of knowledge as well as
> critical mistake
>
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl...
> Yes they are written to disk, although it's often for only a short
> period of time. All database writes, including writes to tempdb
> which is in effect handled just like normal user databases, in SQL
> Server are written to memory first (making the page in memory
> "dirty") and then flushed to disk when the lazy writer process
> gets around to it.
> I guess, in theory, the temp table may be dropped before the lazy
> writer writes that data to disk and so it might not ever make it
> to the disk but I've never tried to analyse that scenario.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Yu6454 wrote:
|||So are you saying that since every database write goes to memory for a temp table and that the write will not take place till later, there will be no performance difference on this point?
Let me ask again, "Will temp tables write to disk at some point in time even during or after the stored procedure is over, OR is there a certain condition where temp table DO NOT write to disk ever?"
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%232WwUHBtFHA.2076@.TK2MSFTNGP14.phx.gbl...
Temp tables vs table variables is a fairly common debate topic.
The person you're talking about is kind of right. Every database write happens to memory first and so, in theory, query performance shouldn't really be hindered by I/O to disk (as that is essentially done asynchronously by a background spid). But this assumes you have no great memory pressure.
IMHO there's really not enough difference, performance-wise, to be terribly concerned about it. Temp tables are just like any other permanent table (except that they are automatically dropped when they go out of scope) and as such SQL Server maintains statistics on the columns in those temp tables (SQL Server does not collect statistics on table variables). As such the query optimiser can often come up with better plans for temp tables (than it can with table variables) and manipulate the data in them faster. However, table variables implement less locking generally...so it can swing the other way too. The main reason I would use a table variable would be if I wanted to return a result set from a UDF, because you can only do that using a table variable. So that would be a functionality reason more so than a performance reason.
Aaron Bertrand (SQL MVP) has a great website called ASPFAQ that has heaps of different articles about different things people ask about SQL Server. Here's what he says about temp tables vs table variables:
Should I use a #temp table or a @.table variable?
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
I "heard" that since SQL Server 2000 caches the database, that temp tables are written to memory before disk? and thus there is no significant performance difference between temp table and table variables if you have lots of memory to begin with.
I really never heard of this, but I really think this person was just trying to cover their butt on their lack of knowledge as well as critical mistake
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl...
Yes they are written to disk, although it's often for only a short period of time. All database writes, including writes to tempdb which is in effect handled just like normal user databases, in SQL Server are written to memory first (making the page in memory "dirty") and then flushed to disk when the lazy writer process gets around to it.
I guess, in theory, the temp table may be dropped before the lazy writer writes that data to disk and so it might not ever make it to the disk but I've never tried to analyse that scenario.
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
are temp tables written to disk? or are they cached like the database is
cached on queries.
|||Hi,
If u talk about Temp Table then u should know the meaning of temporary.
When u create a table using # then MS-SQl Server creates a
table(structure in Buffer to hold the data)
and as u finh working with that it frees the memory. Temporary table
name is basically a alias name for the buffer reserved for
that temporary data.
If u execute a query from QA (select * into #temp from temp) then it
will be in that connection scope till u do not close the connection.
If u execute a Stored Procedure then as the stored produre execution
finished the scope of temo file will be end.
hope this helps u
from
Doller
Mike Hodgson wrote:[vbcol=seagreen]
> Temp tables vs table variables is a fairly common debate topic.
> The person you're talking about is kind of right. Every database write
> happens to memory first and so, in theory, query performance shouldn't
> really be hindered by I/O to disk (as that is essentially done
> asynchronously by a background spid). But this assumes you have no
> great memory pressure.
> IMHO there's really not enough difference, performance-wise, to be
> terribly concerned about it. Temp tables are just like any other
> permanent table (except that they are automatically dropped when they go
> out of scope) and as such SQL Server maintains statistics on the columns
> in those temp tables (SQL Server does *not* collect statistics on table
> variables). As such the query optimiser can often come up with better
> plans for temp tables (than it can with table variables) and manipulate
> the data in them faster. However, table variables implement less
> locking generally...so it can swing the other way too. The main reason
> I would use a table variable would be if I wanted to return a result set
> from a UDF, because you can only do that using a table variable. So
> that would be a functionality reason more so than a performance reason.
> Aaron Bertrand
> <http://www.microsoft.com/communities...taSvcParams%5e>
> (SQL MVP) has a great website called ASPFAQ <http://www.aspfaq.com> that
> has heaps of different articles about different things people ask about
> SQL Server. Here's what he says about temp tables vs table variables:
> Should I use a #temp table or a @.table variable?
> <http://www.aspfaq.com/show.asp?id=2475>
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Yu6454 wrote:
|||That really did NOT help.
Is there a case or situation where a #temp table writes to DISK?
And if so, what is this case or situation where a temp table is written to
disk?
"doller" <sufianarif@.gmail.com> wrote in message
news:1126143644.984768.213460@.z14g2000cwz.googlegr oups.com...[vbcol=seagreen]
> Hi,
> If u talk about Temp Table then u should know the meaning of temporary.
> When u create a table using # then MS-SQl Server creates a
> table(structure in Buffer to hold the data)
> and as u finh working with that it frees the memory. Temporary table
> name is basically a alias name for the buffer reserved for
> that temporary data.
> If u execute a query from QA (select * into #temp from temp) then it
> will be in that connection scope till u do not close the connection.
> If u execute a Stored Procedure then as the stored produre execution
> finished the scope of temo file will be end.
> hope this helps u
> from
> Doller
>
>
> Mike Hodgson wrote:
<http://www.microsoft.com/communities...ms=%7eCMTYData
SvcParams%5e%7earg+Name%3d%22guid%22+Value%3d%22cf 9c9e5b-abcf-4495-925b-0cd5
15fcdc89%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e% 2fCMTYDataSvcParams%5e>[vbcol=seagreen]
is
>
|||You should just keep your thoughts to yourself if you have nothing
useful to add. (BTW, I fully understand the difference between, and
scope of, a permanent table, a local temp table and global temp table.)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
doller wrote:

>Hi,
>If u talk about Temp Table then u should know the meaning of temporary.
>When u create a table using # then MS-SQl Server creates a
>table(structure in Buffer to hold the data)
>and as u finh working with that it frees the memory. Temporary table
>name is basically a alias name for the buffer reserved for
>that temporary data.
>If u execute a query from QA (select * into #temp from temp) then it
>will be in that connection scope till u do not close the connection.
>If u execute a Stored Procedure then as the stored produre execution
>finished the scope of temo file will be end.
>hope this helps u
>from
>Doller
>
>
>Mike Hodgson wrote:
>
>
>
|||In thinking this through logically in my own mind, I would say that
writes to temp tables will always result in one or more writes to disk
(although that write may be deferred). This is because once the page is
marked dirty in RAM, even if the page is changed yet again at a later
stage to its original values (by dropping the temp table), the lazy
writer will come along and flush that page to disk (i.e. copy what's in
memory for that 8K page and write in in the appropriate place in the
relevant data file). The temp table getting dropped (whether
automatically or manually) will not reset the dirty page status; only
flushing the page to disk will reset the status of that page.
Prior to SQL 7 you used to be able to force SQL Server to materialise
tempdb only in RAM, which would mean temp table writes wouldn't make it
to disk, but that's no longer supported in SQL 2000 (tempdb is always on
disk).
If nothing else, the transactions associated with creating, populating,
manipulating and dropping the temp table result in the creation of
transaction log records. With tempdb those records are continually
truncated but not before they have been committed & flushed to disk. So
temp table writes will result in transaction log I/O activity in tempdb.
These are just my thoughts and are based on my understanding of how SQL
Server works, not on any particular whitepaper I've read or anything
like that. Perhaps someone better qualified (like Kalen Delaney, Tibor
Karaszi, Andrew Kelly, Paul Randal, Mike Epprecht, etc.) might be able
to clarify more.
Having said that, this whitepaper does a pretty good job of explaining
SQL I/O including flushing dirty pages, async I/O, lazy writes and
flushing log records, but skimming it I can't see that it addresses your
specific question exactly, but is very good background info about SQL I/O:
SQL Server 2000 I/O Basics
<http://www.microsoft.com/technet/pro...lIObasics.mspx>
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
[vbcol=seagreen]
> So are you saying that since every database write goes to memory for a
> temp table and that the write will not take place till later, there
> will be no performance difference on this point?
> Let me ask again, "Will temp tables write to disk at some point in
> time even during or after the stored procedure is over, OR is there a
> certain condition where temp table DO NOT write to disk ever?"
>
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:%232WwUHBtFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Temp tables vs table variables is a fairly common debate topic.
> The person you're talking about is kind of right. Every database
> write happens to memory first and so, in theory, query performance
> shouldn't really be hindered by I/O to disk (as that is
> essentially done asynchronously by a background spid). But this
> assumes you have no great memory pressure.
> IMHO there's really not enough difference, performance-wise, to be
> terribly concerned about it. Temp tables are just like any other
> permanent table (except that they are automatically dropped when
> they go out of scope) and as such SQL Server maintains statistics
> on the columns in those temp tables (SQL Server does *not* collect
> statistics on table variables). As such the query optimiser can
> often come up with better plans for temp tables (than it can with
> table variables) and manipulate the data in them faster. However,
> table variables implement less locking generally...so it can swing
> the other way too. The main reason I would use a table variable
> would be if I wanted to return a result set from a UDF, because
> you can only do that using a table variable. So that would be a
> functionality reason more so than a performance reason.
> Aaron Bertrand
> <http://www.microsoft.com/communities...taSvcParams%5e>
> (SQL MVP) has a great website called ASPFAQ
> <http://www.aspfaq.com> that has heaps of different articles about
> different things people ask about SQL Server. Here's what he says
> about temp tables vs table variables:
> Should I use a #temp table or a @.table variable?
> <http://www.aspfaq.com/show.asp?id=2475>
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Yu6454 wrote:
|||Ok, it sounds that temp tables in the tempdb will somehow. and in some way. eventually some other time, make a write to disk.
Thus, the person who I heard that temp tables have the same performance with regards to table variables is wrong as temp tables somehow write to disk. And in so writing to disk, temp tables would be a performance hit at some point in time.
Thus, one should try to use temp variables just like it says from microsoft "if you can and if you need to" before using temp tables. right? I know when I did and it makes a huge difference
http://support.microsoft.com/default...b;en-us;305977
[at the very bottom of the article]
" In general, you use table variables whenever possible except when there is a significant volume of data and there is repeated use of the table. In that case, you can create indexes on the temporary table to increase query performance. However, each scenario may be different. Microsoft recommends that you test if table variables are more helpful than temporary tables for a particular query or stored procedure."
NEVERTHELESS, this part below is a really bad answer from microsoft does NOT seem true in REAL LIFE as I know from experience, whether I had enough memory or not, table variables are done in memory first, and then , I THINK, overflow are written to disk if necessary. As for Temp Tables, well, one is not sure what's going on, is it memory or disk? And who is to say SQL Server is managing this memory correctly anyway and if it's flushed in time for the next stored procedure execution.
Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?
A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uKiOl1BtFHA.1168@.TK2MSFTNGP10.phx.gbl...
In thinking this through logically in my own mind, I would say that writes to temp tables will always result in one or more writes to disk (although that write may be deferred). This is because once the page is marked dirty in RAM, even if the page is changed yet again at a later stage to its original values (by dropping the temp table), the lazy writer will come along and flush that page to disk (i.e. copy what's in memory for that 8K page and write in in the appropriate place in the relevant data file). The temp table getting dropped (whether automatically or manually) will not reset the dirty page status; only flushing the page to disk will reset the status of that page.
Prior to SQL 7 you used to be able to force SQL Server to materialise tempdb only in RAM, which would mean temp table writes wouldn't make it to disk, but that's no longer supported in SQL 2000 (tempdb is always on disk).
If nothing else, the transactions associated with creating, populating, manipulating and dropping the temp table result in the creation of transaction log records. With tempdb those records are continually truncated but not before they have been committed & flushed to disk. So temp table writes will result in transaction log I/O activity in tempdb.
These are just my thoughts and are based on my understanding of how SQL Server works, not on any particular whitepaper I've read or anything like that. Perhaps someone better qualified (like Kalen Delaney, Tibor Karaszi, Andrew Kelly, Paul Randal, Mike Epprecht, etc.) might be able to clarify more.
Having said that, this whitepaper does a pretty good job of explaining SQL I/O including flushing dirty pages, async I/O, lazy writes and flushing log records, but skimming it I can't see that it addresses your specific question exactly, but is very good background info about SQL I/O:
SQL Server 2000 I/O Basics
Hope this helps.
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
So are you saying that since every database write goes to memory for a temp table and that the write will not take place till later, there will be no performance difference on this point?
Let me ask again, "Will temp tables write to disk at some point in time even during or after the stored procedure is over, OR is there a certain condition where temp table DO NOT write to disk ever?"
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%232WwUHBtFHA.2076@.TK2MSFTNGP14.phx.gbl...
Temp tables vs table variables is a fairly common debate topic.
The person you're talking about is kind of right. Every database write happens to memory first and so, in theory, query performance shouldn't really be hindered by I/O to disk (as that is essentially done asynchronously by a background spid). But this assumes you have no great memory pressure.
IMHO there's really not enough difference, performance-wise, to be terribly concerned about it. Temp tables are just like any other permanent table (except that they are automatically dropped when they go out of scope) and as such SQL Server maintains statistics on the columns in those temp tables (SQL Server does not collect statistics on table variables). As such the query optimiser can often come up with better plans for temp tables (than it can with table variables) and manipulate the data in them faster. However, table variables implement less locking generally...so it can swing the other way too. The main reason I would use a table variable would be if I wanted to return a result set from a UDF, because you can only do that using a table variable. So that would be a functionality reason more so than a performance reason.
Aaron Bertrand (SQL MVP) has a great website called ASPFAQ that has heaps of different articles about different things people ask about SQL Server. Here's what he says about temp tables vs table variables:
Should I use a #temp table or a @.table variable?
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
I "heard" that since SQL Server 2000 caches the database, that temp tables are written to memory before disk? and thus there is no significant performance difference between temp table and table variables if you have lots of memory to begin with.
I really never heard of this, but I really think this person was just trying to cover their butt on their lack of knowledge as well as critical mistake
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl...
Yes they are written to disk, although it's often for only a short period of time. All database writes, including writes to tempdb which is in effect handled just like normal user databases, in SQL Server are written to memory first (making the page in memory "dirty") and then flushed to disk when the lazy writer process gets around to it.
I guess, in theory, the temp table may be dropped before the lazy writer writes that data to disk and so it might not ever make it to the disk but I've never tried to analyse that scenario.
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
are temp tables written to disk? or are they cached like the database is
cached on queries.