are temp tables written to disk?

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.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.googlegroups.com...
> 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/communitie...ams=%7eCMTYData
SvcParams%5e%7earg+Name%3d%22guid%22+Val
ue%3d%22cf9c9e5b-abcf-4495-925b-0cd5
15fcdc89%22%2f%5e%7esParams%5e%7e%2fsPar
ams%5e%7e%2fCMTYDataSvcParams%5e>
is
>|||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. A
nd in so writing to disk, temp tables would be a performance hit at some poi
nt 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 whe
n I did and it makes a huge difference
http://support.microsoft.com/defaul...kb;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 tha
t case, you can create indexes on the temporary table to increase query perf
ormance. However, each scenario may be different. Microsoft recommends that
you test if table variables are more helpful than temporary tables for a par
ticular 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 memo
ry or not, table variables are done in memory first, and then , I THINK, ove
rflow 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 n
ext stored procedure execution.
Q4: Are table variables memory-only structures that are assured better perfo
rmance as compared to temporary or permanent tables, because they are mainta
ined in a database that resides on the physical disk?
A4: A table variable is not a memory-only structure. Because a table variabl
e might hold more data than can fit in memory, it has to have a place on dis
k to store data. Table variables are created in the tempdb database similar
to temporary tables. If memory is available, both table variables and tempor
ary tables are created and processed while in memory (data cache).
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uKi
Ol1BtFHA.1168@.TK2MSFTNGP10.phx.gbl...
In thinking this through logically in my own mind, I would say that writes t
o temp tables will always result in one or more writes to disk (although tha
t 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 flu
sh 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 gett
ing dropped (whether automatically or manually) will not reset the dirty pag
e 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, b
ut that's no longer supported in SQL 2000 (tempdb is always on disk).
If nothing else, the transactions associated with creating, populating, mani
pulating and dropping the temp table result in the creation of transaction l
og records. With tempdb those records are continually truncated but not bef
ore they have been committed & flushed to disk. So temp table writes will r
esult in transaction log I/O activity in tempdb.
These are just my thoughts and are based on my understanding of how SQL Serv
er 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 r
ecords, 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 pe
rformance 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 conditi
on where temp table DO NOT write to disk ever?"
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23
2WwUHBtFHA.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 happ
ens 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 ba
ckground 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 (ex
cept that they are automatically dropped when they go out of scope) and as s
uch 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 w
ith 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 r
eturn a result set from a UDF, because you can only do that using a table va
riable. 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. Her
e'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 a
re written to memory before disk? and thus there is no significant performa
nce difference between temp table and table variables if you have lots of me
mory 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:em9
qWbAtFHA.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 h
andled 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 wri
tes 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 think the take home message is test it.
Try both certainly. Sometimes temp tables will be quicker, sometimes
table variables will be quicker. Each has their place. When you need
to create indexes on your temporary objects - temp tables. When you
need to return datasets from functions (and not just a single scalar) -
table variables. Scope is definitely simpler with table variables. The
reason there are less recompilations of procs using table variables (as
opposed to temp tables) is because the query optimiser doesn't need to
come up with a plan of how to access the data in the table variables
(it's always the same) as there is only 1 way, a table scan, but with
temp tables there can be indexes & density statistics, which make many
more access paths possible.
I wouldn't say the person you were talking to was wrong per se. He was
pretty close. And, unless we're talking about HUGE amounts of temp
table/table variable activity, I'd say debating the performance hit due
to I/O is really splitting straws.
I did do a few tests with this though (with 10 sample iterations for
each average) and came up with the following (and this is on a
completely unloaded, dedicated server - dual Xeon, 2GB RAM, local RAID1
array):
_
_*avg batch duration (msec)*
_ #rows_ _table variable_ _temp table_
512 15.1 14.5
1024 17.9 29.4
2048 29.2 39.9
4096 61.8 71.6
8192 127.9 145.7
16384 254.2 291.7
32768 544.6 583.0
65536 1117.4 1103.4
131072 2174.3 2224.2
262144 4377.1 4553.2
524288 8852.1 8402.0
1048576 17846.6 16188.6
2097152 36335.5 32739.4
As you can see, for small batches the duration is almost identical,
although temp tables are, in general slightly slower. And as the
batches become larger the averages tended to drift apart a bit with temp
tables being quicker. In any case, when you graph this, the 2 lines are
almost on top of each other.
The batches consisted of just this loop essentially (with #tmp instead
of @.mytabvar for the temp table runs):
declare @.i int
set @.i = 1
insert into @.mytabvar (blah) values ('This is a line of text')
while (@.i < 22)
begin
insert into @.mytabvar (blah) select blah from @.mytabvar
set @.i = @.i + 1
end
So you can see it was just doing selects & inserts. The temp table #tmp
was defined as exactly the same as @.mytabvar, with no indexes.
Obviously, the difference, performance-wise, between table variables &
temp tables, comes into play much more when things get more complex -
locking contention, many spids executing the same code simultaneously,
indexes, complex queries, etc.
I also noticed that using table variables always resulted in a user
table being created in tempdb, just like with temp tables:
declare @.mytabvar TABLE
(
ID int identity(1,1) not null,
blah varchar(30) null
)
select * from tempdb.dbo.sysobjects where type = 'U'
CREATE TABLE #tmp
(
ID int identity(1,1) not null,
blah varchar(30) null
)
select * from tempdb.dbo.sysobjects where type = 'U'
drop table #tmp
*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/defaul...kb;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.
>
>|||I haven't read all the stuff in this thread, so please forgive me if I'm rep
eating or stating the
obvious below. Just my 2 cents:
Temp tables and table variables are handled internally very very similar. Th
ere is slightly less
logging to the transaction log for table variables (can easily be verified c
hecking number of log
records after a set of modifications). So, please don't forget that for both
temp tables and table
variables, you still have writing to transaction log, same way as for regula
r tables (except a bit
less logging, no REDO information).
As for usage, a rough rule is to use table variables for smaller tables, les
s risks for recompiles,
tiny less over head (no statistics). For bigger tables, use temp tables (sta
tistics, you can create
indexes etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:%23j2jMVEtFHA.3908@.tk2msftngp13.phx.gbl...
>I think the take home message is test it.
> Try both certainly. Sometimes temp tables will be quicker, sometimes
> table variables will be quicker. Each has their place. When you need
> to create indexes on your temporary objects - temp tables. When you
> need to return datasets from functions (and not just a single scalar) -
> table variables. Scope is definitely simpler with table variables. The
> reason there are less recompilations of procs using table variables (as
> opposed to temp tables) is because the query optimiser doesn't need to
> come up with a plan of how to access the data in the table variables
> (it's always the same) as there is only 1 way, a table scan, but with
> temp tables there can be indexes & density statistics, which make many
> more access paths possible.
> I wouldn't say the person you were talking to was wrong per se. He was
> pretty close. And, unless we're talking about HUGE amounts of temp
> table/table variable activity, I'd say debating the performance hit due
> to I/O is really splitting straws.
> I did do a few tests with this though (with 10 sample iterations for
> each average) and came up with the following (and this is on a
> completely unloaded, dedicated server - dual Xeon, 2GB RAM, local RAID1
> array):
> _
> _*avg batch duration (msec)*
> _ #rows_ _table variable_ _temp table_
> 512 15.1 14.5
> 1024 17.9 29.4
> 2048 29.2 39.9
> 4096 61.8 71.6
> 8192 127.9 145.7
> 16384 254.2 291.7
> 32768 544.6 583.0
> 65536 1117.4 1103.4
> 131072 2174.3 2224.2
> 262144 4377.1 4553.2
> 524288 8852.1 8402.0
> 1048576 17846.6 16188.6
> 2097152 36335.5 32739.4
> As you can see, for small batches the duration is almost identical,
> although temp tables are, in general slightly slower. And as the
> batches become larger the averages tended to drift apart a bit with temp
> tables being quicker. In any case, when you graph this, the 2 lines are
> almost on top of each other.
> The batches consisted of just this loop essentially (with #tmp instead
> of @.mytabvar for the temp table runs):
> declare @.i int
> set @.i = 1
> insert into @.mytabvar (blah) values ('This is a line of text')
> while (@.i < 22)
> begin
> insert into @.mytabvar (blah) select blah from @.mytabvar
> set @.i = @.i + 1
> end
> So you can see it was just doing selects & inserts. The temp table #tmp
> was defined as exactly the same as @.mytabvar, with no indexes.
> Obviously, the difference, performance-wise, between table variables &
> temp tables, comes into play much more when things get more complex -
> locking contention, many spids executing the same code simultaneously,
> indexes, complex queries, etc.
> I also noticed that using table variables always resulted in a user
> table being created in tempdb, just like with temp tables:
> declare @.mytabvar TABLE
> (
> ID int identity(1,1) not null,
> blah varchar(30) null
> )
> select * from tempdb.dbo.sysobjects where type = 'U'
> CREATE TABLE #tmp
> (
> ID int identity(1,1) not null,
> blah varchar(30) null
> )
> select * from tempdb.dbo.sysobjects where type = 'U'
> drop table #tmp
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Yu6454 wrote:
>
>|||Also have a look at
http://toponewithties.blogspot.com/...nd.h
tml
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eevY$dEtFHA.1136@.TK2MSFTNGP12.phx.gbl...
>I haven't read all the stuff in this thread, so please forgive me if I'm
>repeating or stating the obvious below. Just my 2 cents:
> Temp tables and table variables are handled internally very very similar.
> There is slightly less logging to the transaction log for table variables
> (can easily be verified checking number of log records after a set of
> modifications). So, please don't forget that for both temp tables and
> table variables, you still have writing to transaction log, same way as
> for regular tables (except a bit less logging, no REDO information).
> As for usage, a rough rule is to use table variables for smaller tables,
> less risks for recompiles, tiny less over head (no statistics). For bigger
> tables, use temp tables (statistics, you can create indexes etc).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
> news:%23j2jMVEtFHA.3908@.tk2msftngp13.phx.gbl...
>|||>
http://toponewithties.blogspot.com/...nd.h
tml
Very informative post. You should remove #8, however. Inserts in SQL
Server never generate a parallel execution plan. The source select or exec
may execute in parallel, but the insert itself--including the generation of
IDENTITY values--always executes in series. This behavior is the same
regardless of whether you're inserting into a table variable, a temporary
table or a normal table. Therefore, there is no difference as far as
parallelism goes between a table variable and a local temporary table.
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:uuqGmIFtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> Also have a look at
>
http://toponewithties.blogspot.com/...riable-and.html[color
=darkred]
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote[/color]
in
> message news:eevY$dEtFHA.1136@.TK2MSFTNGP12.phx.gbl...
similar.
variables
bigger
The
temp
are
#tmp
>|||Additionally, I would like to add that this is an insert.
But in regards to performance, an important category is when there are simul
taneous executions like from a web orders system. So I am guessing an large
number of spid's if I am correct in using that term to begin with.
Reading this article explanation of when table variables and temp tables wri
te to disk is very POOR.
http://support.microsoft.com/defaul...kb;en-us;305977
Q4: Are table variables memory-only structures that are assured better perfo
rmance as compared to temporary or permanent tables, because they are mainta
ined in a database that resides on the physical disk?
A4: A table variable is not a memory-only structure. Because a table variabl
e might hold more data than can fit in memory, it has to have a place on dis
k to store data. Table variables are created in the tempdb database similar
to temporary tables. If memory is available, both table variables and tempor
ary tables are created and processed while in memory (data cache).
It's very unclear of when a table variable writes to disk, it just says, "it
can write to disk if there is need to write to disk when there is no memory
left. HUGE DIFFERENCE.
THIS IS WHAT I THINK.
Table Variables, as microsoft said before, should be used before temp tables
if the data is not huge or very large and several other things. Table varia
bles also clean up after themselves with regard to memory whereas temp table
s it's anyone's guess especially if there are multiple spids and the same sp
roc is being called over and over again.
One has got to ask themselves why table variable were created in SQL 2000 to
begin with versus temp tables that were already available in SQL 7.Thus, my
reasoning is table variables are faster by far as SQL 2000 will use RAM fir
st before going to disk. But with Temp tables it could easily start writing
to disk when you don't want it to even though there is plenty of RAM not in
use.
I am also things SCOPE is very important with regards to memory usage. As fo
r Temp Tables, some please enlighten me on scope as that has to be managed r
ight?
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:#j2
jMVEtFHA.3908@.tk2msftngp13.phx.gbl...
I think the take home message is test it.
Try both certainly. Sometimes temp tables will be quicker, sometimes table
variables will be quicker. Each has their place. When you need to create i
ndexes on your temporary objects - temp tables. When you need to return dat
asets from functions (and not just a single scalar) - table variables. Scop
e is definitely simpler with table variables. The reason there are less rec
ompilations of procs using table variables (as opposed to temp tables) is be
cause the query optimiser doesn't need to come up with a plan of how to acce
ss the data in the table variables (it's always the same) as there is only 1
way, a table scan, but with temp tables there can be indexes & density stat
istics, which make many more access paths possible.
I wouldn't say the person you were talking to was wrong per se. He was pret
ty close. And, unless we're talking about HUGE amounts of temp table/table
variable activity, I'd say debating the performance hit due to I/O is really
splitting straws.
I did do a few tests with this though (with 10 sample iterations for each av
erage) and came up with the following (and this is on a completely unloaded,
dedicated server - dual Xeon, 2GB RAM, local RAID1 array):
avg batch duration (msec)
#rows table variable temp table
512 15.1 14.5
1024 17.9 29.4
2048 29.2 39.9
4096 61.8 71.6
8192 127.9 145.7
16384 254.2 291.7
32768 544.6 583.0
65536 1117.4 1103.4
131072 2174.3 2224.2
262144 4377.1 4553.2
524288 8852.1 8402.0
1048576 17846.6 16188.6
2097152 36335.5 32739.4
As you can see, for small batches the duration is almost identical, although
temp tables are, in general slightly slower. And as the batches become lar
ger the averages tended to drift apart a bit with temp tables being quicker.
In any case, when you graph this, the 2 lines are almost on top of each ot
her.
The batches consisted of just this loop essentially (with #tmp instead of @.m
ytabvar for the temp table runs):
declare @.i int
set @.i = 1
insert into @.mytabvar (blah) values ('This is a line of text')
while (@.i < 22)
begin
insert into @.mytabvar (blah) select blah from @.mytabvar
set @.i = @.i + 1
end
So you can see it was just doing selects & inserts. The temp table #tmp was
defined as exactly the same as @.mytabvar, with no indexes. Obviously, the
difference, performance-wise, between table variables & temp tables, comes i
nto play much more when things get more complex - locking contention, many s
pids executing the same code simultaneously, indexes, complex queries, etc.
I also noticed that using table variables always resulted in a user table be
ing created in tempdb, just like with temp tables:
declare @.mytabvar TABLE
(
ID int identity(1,1) not null,
blah varchar(30) null
)
select * from tempdb.dbo.sysobjects where type = 'U'
CREATE TABLE #tmp
(
ID int identity(1,1) not null,
blah varchar(30) null
)
select * from tempdb.dbo.sysobjects where type = 'U'
drop table #tmp
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. A
nd in so writing to disk, temp tables would be a performance hit at some poi
nt 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 whe
n I did and it makes a huge difference
http://support.microsoft.com/defaul...kb;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 tha
t case, you can create indexes on the temporary table to increase query perf
ormance. However, each scenario may be different. Microsoft recommends that
you test if table variables are more helpful than temporary tables for a par
ticular 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 memo
ry or not, table variables are done in memory first, and then , I THINK, ove
rflow 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 n
ext stored procedure execution.
Q4: Are table variables memory-only structures that are assured better perfo
rmance as compared to temporary or permanent tables, because they are mainta
ined in a database that resides on the physical disk?
A4: A table variable is not a memory-only structure. Because a table variabl
e might hold more data than can fit in memory, it has to have a place on dis
k to store data. Table variables are created in the tempdb database similar
to temporary tables. If memory is available, both table variables and tempor
ary tables are created and processed while in memory (data cache).
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uKi
Ol1BtFHA.1168@.TK2MSFTNGP10.phx.gbl...
In thinking this through logically in my own mind, I would say that writes t
o temp tables will always result in one or more writes to disk (although tha
t 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 flu
sh 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 gett
ing dropped (whether automatically or manually) will not reset the dirty pag
e 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, b
ut that's no longer supported in SQL 2000 (tempdb is always on disk).
If nothing else, the transactions associated with creating, populating, mani
pulating and dropping the temp table result in the creation of transaction l
og records. With tempdb those records are continually truncated but not bef
ore they have been committed & flushed to disk. So temp table writes will r
esult in transaction log I/O activity in tempdb.
These are just my thoughts and are based on my understanding of how SQL Serv
er 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 r
ecords, 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 pe
rformance 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 conditi
on where temp table DO NOT write to disk ever?"
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23
2WwUHBtFHA.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 happ
ens 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 ba
ckground 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 (ex
cept that they are automatically dropped when they go out of scope) and as s
uch 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 w
ith 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 r
eturn a result set from a UDF, because you can only do that using a table va
riable. 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. Her
e'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 a
re written to memory before disk? and thus there is no significant performa
nce difference between temp table and table variables if you have lots of me
mory 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:em9
qWbAtFHA.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 h
andled 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 wri
tes 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.|||Brian,
8. Parallelism is not possible when you insert into a table variable.
I got the above information from the NG Posts of SQL MVP Erland
Sommarskog.
In several posts Sommarskog confirms that "When you insert
into a table variable, SQL Server can because of implementation reasons
not use parallellism. "
http://tinyurl.com/7znmx
If you search this group, you can also find SK and Tom confirming the
same.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:eqYkfpFtFHA.4076@.TK2MSFTNGP11.phx.gbl...
> http://toponewithties.blogspot.com/...nd
.html
> Very informative post. You should remove #8, however. Inserts in SQL
> Server never generate a parallel execution plan. The source select or
> exec
> may execute in parallel, but the insert itself--including the generation
> of
> IDENTITY values--always executes in series. This behavior is the same
> regardless of whether you're inserting into a table variable, a temporary
> table or a normal table. Therefore, there is no difference as far as
> parallelism goes between a table variable and a local temporary table.
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:uuqGmIFtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> http://toponewithties.blogspot.com/...nd
.html
> in
> similar.
> variables
> bigger
> The
> temp
> are
> #tmp
>|||> Table variables also clean up after themselves with regard to memory
> whereas temp tables it's anyone's guess especially if there are multiple
> spids and the same sproc is being called over and over again.
Why on earth would you think this is "anyone's guess"? Temp tables are
cleaned up when the procedure or batch or session goes out of scope. And
each spid gets its own scope (and hence each spid that calls the same SP
gets its own copy of the temp table), so if the implication is that SQL
Server gets when multiple spids are calling the same proc, then you
are wrong.

> Thus, my reasoning is table variables are faster by far as SQL 2000 will
> use RAM first before going to disk.
I don't think you've entirely read all of the content before making your
assumptions. Maybe you should read this article as well:
http://www.aspfaq.com/2475