sql nulls slow: are temp tables written to disk?

are temp tables written to disk? or are they cached like the database is
cached on queries.This is a multi-part message in MIME format.
--010504000405040008070302
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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.
>
>
--010504000405040008070302
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Yu6454 wrote:
<blockquote cite="mid%23iB48RAtFHA.996@.TK2MSFTNGP14.phx.gbl" type="cite">
<pre wrap="">are temp tables written to disk? or are they cached like the database is
cached on queries.
</pre>
</blockquote>
</body>
</html>
--010504000405040008070302--|||This is a multi-part message in MIME format.
--=_NextPart_000_000B_01C5B3CF.1CC4AA20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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=20
Yu6454 wrote: are temp tables written to disk? or are they cached like the database =is
cached on queries.
--=_NextPart_000_000B_01C5B3CF.1CC4AA20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

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" 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 =hodgsonblog: http://sqlnerd.blogspot.com Yu6454 wrote: are temp tables written to disk? or are =they cached like the database is
cached on queries.

--=_NextPart_000_000B_01C5B3CF.1CC4AA20--|||This is a multi-part message in MIME format.
--090805080202030906010105
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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/mvp/mvpdetails.mspx?Params=%7eCMTYDataSvcParams%5e%7earg+Name%3d%22guid%22+Value%3d%22cf9c9e5b-abcf-4495-925b-0cd515fcdc89%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParams%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:
> 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:
>>are temp tables written to disk? or are they cached like the database is
>>cached on queries.
>>
>>
--090805080202030906010105
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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. 
 
<a
href="http://links.10026.com/?link=Aaron">http://www.microsoft.com/communities/mvp/mvpdetails.mspx?Params=%7eCMTYDataSvcParams%5e%7earg+Name%3d%22guid%22+Value%3d%22cf9c9e5b-abcf-4495-925b-0cd515fcdc89%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParams%5e">Aaron
Bertrand</a> (SQL MVP) has a great website called <a
href="http://links.10026.com/?link=aspfaq</a>">http://www.aspfaq.com">ASPFAQ</a> 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: 
<a href="http://links.10026.com/?link=Should">http://www.aspfaq.com/show.asp?id=2475">Should I use a #temp
table or a @.table variable?</a> 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Yu6454 wrote:
<blockquote cite="mid%23vp21nAtFHA.1284@.tk2msftngp13.phx.gbl"
type="cite">
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<meta content="MSHTML 6.00.2800.1515" name="GENERATOR">
<style></style>
<div>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. </div>
<div> </div>
<div>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</div>
<div> </div>
<div> </div>
<div> </div>
<div> </div>
<blockquote
style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;"
dir="ltr">
<div>"Mike Hodgson" <<a
href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mike.hodgson@.mallesons.nospam.com</a>>
wrote in message <a href="http://links.10026.com/?link=news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl">news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl</a>...</div>
<tt>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. 
</tt>
<div class="moz-signature">
-- 
mike
hodgson 
blog: <a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Yu6454 wrote:
<blockquote cite="mid%23iB48RAtFHA.996@.TK2MSFTNGP14.phx.gbl"
type="cite">
<pre wrap="">are temp tables written to disk? or are they cached like the database is
cached on queries.
</pre>
</blockquote>
</blockquote>
</blockquote>
</body>
</html>
--090805080202030906010105--|||This is a multi-part message in MIME format.
--=_NextPart_000_000F_01C5B3DA.EC667FF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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=20
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=20
Yu6454 wrote: are temp tables written to disk? or are they cached like the database =is
cached on queries.
--=_NextPart_000_000F_01C5B3DA.EC667FF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

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" 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 http://www.aspfaq.com">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:http://www.aspfaq.com/show.asp?id=3D2475">Should I use a #temp =table or a @.table variable?
--mike =hodgsonblog: 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" 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 =hodgsonblog: http://sqlnerd.blogspot.com Yu6454 wrote: are temp tables written to disk? or are =they cached like the database is
cached on queries.
=

--=_NextPart_000_000F_01C5B3DA.EC667FF0--|||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:
> 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/mvp/mvpdetails.mspx?Params=%7eCMTYDataSvcParams%5e%7earg+Name%3d%22guid%22+Value%3d%22cf9c9e5b-abcf-4495-925b-0cd515fcdc89%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParams%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:
> > 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:
> >
> >>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:
> > 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/mvp/mvpdetails.mspx?Params=%7eCMTYData
SvcParams%5e%7earg+Name%3d%22guid%22+Value%3d%22cf9c9e5b-abcf-4495-925b-0cd5
15fcdc89%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParams%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:
> >
> > > 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:
> > >
> > >>are temp tables written to disk? or are they cached like the database
is
> > >>cached on queries.
> > >>
> > >>
> > >>
> > >>
>|||This is a multi-part message in MIME format.
--040201010103000306080500
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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:
>
>>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/mvp/mvpdetails.mspx?Params=%7eCMTYDataSvcParams%5e%7earg+Name%3d%22guid%22+Value%3d%22cf9c9e5b-abcf-4495-925b-0cd515fcdc89%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParams%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:
>>
>>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:
>>
>>are temp tables written to disk? or are they cached like the database is
>>cached on queries.
>>
>>
>>
>
>
--040201010103000306080500
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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.)</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
doller wrote:
<blockquote
cite="mid1126143644.984768.213460@.z14g2000cwz.googlegroups.com"
type="cite">
<pre wrap="">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:
</pre>
<blockquote type="cite">
<pre wrap="">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
<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=<http://www.microsoft.com/communities/mvp/mvpdetails.mspx?Params=~CMTYDataSvcParams%5e~arg+Name%3d%22guid%22+Value%3d%22cf9c9e5b-abcf-4495-925b-0cd515fcdc89%22%2f%5e~sParams%5e~%2fsParams%5e~%2fCMTYDataSvcParams%5e></a>">http://www.microsoft.com/communities/mvp/mvpdetails.mspx?Params=%7eCMTYDataSvcParams%5e%7earg+Name%3d%22guid%22+Value%3d%22cf9c9e5b-abcf-4495-925b-0cd515fcdc89%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParams%5e"><http://www.microsoft.com/communities/mvp/mvpdetails.mspx?Params=%7eCMTYDataSvcParams%5e%7earg+Name%3d%22guid%22+Value%3d%22cf9c9e5b-abcf-4495-925b-0cd515fcdc89%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParams%5e></a>
(SQL MVP) has a great website called ASPFAQ <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=<http://www.aspfaq.com></a>">http://www.aspfaq.com"><http://www.aspfaq.com></a> 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?
<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=<http://www.aspfaq.com/show.asp?id=2475></a>">http://www.aspfaq.com/show.asp?id=2475"><http://www.aspfaq.com/show.asp?id=2475></a>
--
*mike hodgson*
blog: <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>
Yu6454 wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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" <<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mike.hodgson@.mallesons.nospam.com</a>
<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com"><mailto:mike.hodgson@.mallesons.nospam.com></a>> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl">news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl</a>...
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: <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>
Yu6454 wrote:
</pre>
<blockquote type="cite">
<pre wrap="">are temp tables written to disk? or are they cached like the database is
cached on queries.
</pre>
</blockquote>
</blockquote>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--040201010103000306080500--|||This is a multi-part message in MIME format.
--010903040001000704090908
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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/prodtechnol/sql/2000/maintain/sqlIObasics.mspx>
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
> <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/mvp/mvpdetails.mspx?Params=%7eCMTYDataSvcParams%5e%7earg+Name%3d%22guid%22+Value%3d%22cf9c9e5b-abcf-4495-925b-0cd515fcdc89%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParams%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:
>> 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:
>>are temp tables written to disk? or are they cached like the database is
>>cached on queries.
>>
>>
--010903040001000704090908
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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: 
<a
href="http://links.10026.com/?link=SQL">http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx">SQL
Server 2000 I/O Basics</a> 
 
Hope this helps. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Yu6454 wrote:
<blockquote cite="midunhbFXBtFHA.616@.TK2MSFTNGP11.phx.gbl" type="cite">
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<meta content="MSHTML 6.00.2800.1515" name="GENERATOR">
<div>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?</div>
<div> </div>
<div>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?"</div>
<div> </div>
<div> </div>
<div> </div>
<blockquote
style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;"
dir="ltr">
<div>"Mike Hodgson" <<a
href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mike.hodgson@.mallesons.nospam.com</a>>
wrote in message <a href="http://links.10026.com/?link=news:%232WwUHBtFHA.2076@.TK2MSFTNGP14.phx.gbl">news:%232WwUHBtFHA.2076@.TK2MSFTNGP14.phx.gbl</a>...</div>
<tt>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. 
 
<a
href="http://links.10026.com/?link=Aaron">http://www.microsoft.com/communities/mvp/mvpdetails.mspx?Params=%7eCMTYDataSvcParams%5e%7earg+Name%3d%22guid%22+Value%3d%22cf9c9e5b-abcf-4495-925b-0cd515fcdc89%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParams%5e">Aaron
Bertrand</a> (SQL MVP) has a great website called <a
href="http://links.10026.com/?link=aspfaq</a>">http://www.aspfaq.com">ASPFAQ</a> 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: 
<a href="http://links.10026.com/?link=Should">http://www.aspfaq.com/show.asp?id=2475">Should I use a
#temp table or a @.table variable?</a> 
</tt>
<div class="moz-signature">
-- 
mike
hodgson 
blog: <a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Yu6454 wrote:
<blockquote cite="mid%23vp21nAtFHA.1284@.tk2msftngp13.phx.gbl"
type="cite">
<meta content="MSHTML 6.00.2800.1515" name="GENERATOR">
<style></style>
<div>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. </div>
<div> </div>
<div>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</div>
<div> </div>
<div> </div>
<div> </div>
<div> </div>
<blockquote
style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;"
dir="ltr">
<div>"Mike Hodgson" <<a
href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mike.hodgson@.mallesons.nospam.com</a>>
wrote in message <a href="http://links.10026.com/?link=news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl">news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl</a>...</div>
<tt>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. 
</tt>
<div class="moz-signature">
-- 
mike
hodgson 
blog: <a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Yu6454 wrote:
<blockquote cite="mid%23iB48RAtFHA.996@.TK2MSFTNGP14.phx.gbl"
type="cite">
<pre wrap="">are temp tables written to disk? or are they cached like the database is
cached on queries.
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</body>
</html>
--010903040001000704090908--|||This is a multi-part message in MIME format.
--=_NextPart_000_0013_01C5B3ED.3EC86A30
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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.aspx?scid=3Dkb;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=20
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=20
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=20
Yu6454 wrote: are temp tables written to disk? or are they cached like the database =is
cached on queries.
--=_NextPart_000_0013_01C5B3ED.3EC86A30
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

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

[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" =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 BasicsHope this =helps.--mike hodgsonblog: 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" = 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 hodgsonblog: 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" 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 =hodgsonblog: http://sqlnerd.blogspot.com Yu6454 wrote: are temp =tables written to disk? or are they cached like the database iscached =on queries.

--=_NextPart_000_0013_01C5B3ED.3EC86A30--|||This is a multi-part message in MIME format.
--010305050609030706000004
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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/default.aspx?scid=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.
>
>
--010305050609030706000004
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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 </tt><tt> (and this is on
a completely unloaded, dedicated server - dual Xeon, 2GB RAM, local
RAID1 array)</tt><tt>: 
</tt><tt> 
</tt><tt>avg batch duration (msec)</tt> 
<tt> #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): 
</tt>
<blockquote><tt>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 
</tt></blockquote>
<tt>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: 
</tt>
<blockquote><tt>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</tt> 
</blockquote>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Yu6454 wrote:
<blockquote cite="mid%23qYCYgCtFHA.664@.tk2msftngp13.phx.gbl" type="cite">
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<meta content="MSHTML 6.00.2800.1515" name="GENERATOR">
<style></style>
<div>Ok, it sounds that temp tables in
the tempdb will somehow. and in some way. eventually some other time,
make a write to disk. </div>
<div> </div>
<div>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.</div>
<div> </div>
<div>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</div>
<div> </div>
<div><a
href="http://links.10026.com/?link=http://support.microsoft.com/default.aspx?scid=kb;en-us;305977</a></div>">http://support.microsoft.com/default.aspx?scid=kb;en-us;305977">http://support.microsoft.com/default.aspx?scid=kb;en-us;305977</a></div>
<div> </div>
<blockquote style="margin-right: 0px;" dir="ltr">
<div>[at the very bottom of the article]</div>
<div>" 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."</div>
</blockquote>
<div> 
</div>
<div>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.</div>
<div> </div>
<div> </div>
<blockquote style="margin-right: 0px;" dir="ltr">
 
<div> 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). </div>
</blockquote>
<div> 
 
"Mike Hodgson" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com"><mike.hodgson@.mallesons.nospam.com></a> wrote in
message <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:uKiOl1BtFHA.1168@.TK2MSFTNGP10.phx.gbl">news:uKiOl1BtFHA.1168@.TK2MSFTNGP10.phx.gbl</a>... 
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: <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a> 
 
 
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" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com"><mike.hodgson@.mallesons.nospam.com></a> wrote in
message <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:%232WwUHBtFHA.2076@.TK2MSFTNGP14.phx.gbl">news:%232WwUHBtFHA.2076@.TK2MSFTNGP14.phx.gbl</a>... 
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: <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a> 
 
 
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" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com"><mike.hodgson@.mallesons.nospam.com></a> wrote in
message <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl">news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl</a>... 
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: <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a> 
 
 
Yu6454 wrote: 
are temp tables written to disk? or are they cached like the database
is 
cached on queries. 
 
 
</div>
</blockquote>
</body>
</html>
--010305050609030706000004--|||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...
>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/default.aspx?scid=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.
>>
>|||Also have a look at
http://toponewithties.blogspot.com/2004/08/difference-between-table-variable-and.html
--
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...
>>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/default.aspx?scid=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.
>>
>>
>|||Ok,I kept my thought with me .But i think udont have a clue about all
that/.
Now if u understand about temp table then prove how sql server writes a
temp table to disk.
Prove it else i will think u dont have a clue about anything.
form
doler
Mike Hodgson wrote:
> 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:
> >
> >
> >>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/mvp/mvpdetails.mspx?Params=%7eCMTYDataSvcParams%5e%7earg+Name%3d%22guid%22+Value%3d%22cf9c9e5b-abcf-4495-925b-0cd515fcdc89%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParams%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:
> >>
> >>
> >>
> >>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:
> >>
> >>
> >>
> >>are temp tables written to disk? or are they cached like the database is
> >>cached on queries.
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
> >|||>
http://toponewithties.blogspot.com/2004/08/difference-between-table-variable-and.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...
> Also have a look at
>
http://toponewithties.blogspot.com/2004/08/difference-between-table-variable-and.html
> --
> 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...
> >>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/default.aspx?scid=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.
> >>
> >>
> >>
> >>
> >>
> >
>|||Thinks about the question.
Are temp tables written to disk?
If not then why the existance of the TempDB? Unless of course they are
written to disk.
I have tried to find you an answer but it seems there is little written
about it.
So I will make some guesses.
1 - It would guess temp tables are always written to the TempDB and are
cached like any other.
2 - I would guess that variable tables are NOT written to the disk unless
they exceed certain sizes. This would also explain the restrictions on using
indexes, ALTER etc etc that you cannot perform on variable databases because
they are most likely in memory and the database engine is designed to work
on physical databases (TempDB) not memory ones (variables in memory)
This is just supposition but could be proved by careful analysis using
profile, performance monitor and DBCC to dump pages of the tempdb but alas I
do not have the time.
--
Nik Marshall-Blank MCSD/MCDBA
"doller" <sufianarif@.gmail.com> wrote in message
news:1126172338.931270.122790@.o13g2000cwo.googlegroups.com...
> Ok,I kept my thought with me .But i think udont have a clue about all
> that/.
> Now if u understand about temp table then prove how sql server writes a
> temp table to disk.
> Prove it else i will think u dont have a clue about anything.
> form
> doler
> Mike Hodgson wrote:
>> 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:
>> >
>> >
>> >>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/mvp/mvpdetails.mspx?Params=%7eCMTYDataSvcParams%5e%7earg+Name%3d%22guid%22+Value%3d%22cf9c9e5b-abcf-4495-925b-0cd515fcdc89%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParams%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:
>> >>
>> >>
>> >>
>> >>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:
>> >>
>> >>
>> >>
>> >>are temp tables written to disk? or are they cached like the
>> >>database is
>> >>cached on queries.
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>> >
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0025_01C5B427.73FA12A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Additionally, I would like to add that this is an insert.
But in regards to performance, an important category is when there are =simultaneous 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 =write to disk is very POOR.
http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;305977
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).
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 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.
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 first 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 for Temp Tables, some please enlighten me on scope as that has to be =managed right?
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:#j2jMVEtFHA.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 =3D 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 =3D @.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 =3D 'U'
CREATE TABLE #tmp
(
ID int identity(1,1) not null,
blah varchar(30) null
)
select * from tempdb.dbo.sysobjects where type =3D 'U'
drop table #tmp
--
mike hodgson
blog: http://sqlnerd.blogspot.com=20
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.aspx?scid=3Dkb;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=20
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=20
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=20
Yu6454 wrote: are temp tables written to disk? or are they cached like the =database is
cached on queries.
--=_NextPart_000_0025_01C5B427.73FA12A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Additionally, I would like to add that =this is an insert.
But in regards to performance, an =important category is when there are simultaneous 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 write to disk is very POOR.

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). =
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 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.
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 first 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 for Temp Tables, some please enlighten me on scope as =that has to be managed right?

"Mike Hodgson" wrote in message news:#j2jMVEtFHA.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.01048576 = 17846.6 16188.62097152 36335.5 32739.4As =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 intset @.i =3D 1insert into =@.mytabvar (blah) values ('This is a line of text')while (@.i < 22) begin insert into =@.mytabvar (blah) select blah from @.mytabvar set @.i =3D =@.i + 1 endSo 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 =3D 'U'CREATE TABLE #tmp ( ID int =identity(1,1) not null, blah varchar(30) =null )select * from tempdb.dbo.sysobjects where type =3D ='U'drop table #tmp
--mike =hodgsonblog: 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

[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" 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 BasicsHope this helps.--mike hodgsonblog: http://sqlnerd.blogspot.com">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" 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 hodgsonblog: http://sqlnerd.blogspot.com">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" 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 hodgsonblog: http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com = Yu6454 wrote: are temp tables written to disk? =or are they cached like the database iscached on =queries.

--=_NextPart_000_0025_01C5B427.73FA12A0--|||This is a multi-part message in MIME format.
--080200010900030703040900
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
I don't really care what you think I know or don't know, but for
education's sake...
How about, run this batch:
begin tran
select * into #tmp from dbo.sysobjects
insert into #tmp select * from #tmp
insert into #tmp select * from #tmp
insert into #tmp select * from #tmp
insert into #tmp select * from #tmp
insert into #tmp select * from #tmp
insert into #tmp select * from #tmp
insert into #tmp select * from #tmp
insert into #tmp select * from #tmp
insert into #tmp select * from #tmp
insert into #tmp select * from #tmp
insert into #tmp select * from #tmp
insert into #tmp select * from #tmp
insert into #tmp select * from #tmp
insert into #tmp select * from #tmp
insert into #tmp select * from #tmp
insert into #tmp select * from #tmp
drop table #tmp
commit tran
And, assuming you start with a small tempdb (like 20MB or less), watch
the size of your tempdb.mdf and templog.ldf files grow! Or, if you
prefer to watch perfmon counters, run this batch simultaneously:
while (1=1)
begin
select counter_name, cntr_value from master.dbo.sysperfinfo
where counter_name in ('Log Flushes/sec', 'Log Bytes Flushed/sec')
and instance_name = 'tempdb'
waitfor delay '0:00:10'
end
You'll notice the counter values changing, thereby proving that pages
are getting flushed to disk (if watching the physical file sizes
changing was not enough proof for you).
Happy?
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
doller wrote:
>Ok,I kept my thought with me .But i think udont have a clue about all
>that/.
>Now if u understand about temp table then prove how sql server writes a
>temp table to disk.
>Prove it else i will think u dont have a clue about anything.
>form
>doler
>Mike Hodgson wrote:
>
>>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:
>>
>>
>>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/mvp/mvpdetails.mspx?Params=%7eCMTYDataSvcParams%5e%7earg+Name%3d%22guid%22+Value%3d%22cf9c9e5b-abcf-4495-925b-0cd515fcdc89%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParams%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:
>>
>>
>>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:
>>
>>
>>are temp tables written to disk? or are they cached like the database is
>>cached on queries.
>>
>>
>>
>>
>>
>>
>
>
--080200010900030703040900
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I don't really care what you think I know or don't know, but for
education's sake... 
 
How about, run this batch: 
</tt>
<blockquote><tt>begin tran 
 
select * into #tmp from dbo.sysobjects 
 
insert into #tmp select * from #tmp 
insert into #tmp select * from #tmp 
insert into #tmp select * from #tmp 
insert into #tmp select * from #tmp 
insert into #tmp select * from #tmp 
insert into #tmp select * from #tmp 
insert into #tmp select * from #tmp 
insert into #tmp select * from #tmp 
</tt><tt>insert into #tmp select * from #tmp 
insert into #tmp select * from #tmp 
insert into #tmp select * from #tmp 
insert into #tmp select * from #tmp 
</tt><tt>insert into #tmp select * from #tmp 
insert into #tmp select * from #tmp 
insert into #tmp select * from #tmp 
insert into #tmp select * from #tmp 
</tt> 
<tt>drop table #tmp 
 
commit tran 
</tt></blockquote>
<tt>And, assuming you start with a small tempdb (like 20MB or less),
watch the size of your tempdb.mdf and templog.ldf files grow! Or, if
you prefer to watch perfmon counters, run this batch simultaneously: 
</tt>
<blockquote><tt>while (1=1) 
begin 
 
select counter_name, cntr_value from master.dbo.sysperfinfo 
where counter_name in ('Log Flushes/sec', 'Log Bytes Flushed/sec') 
and instance_name = 'tempdb' 
 
waitfor delay '0:00:10' 
end 
</tt></blockquote>
<tt>You'll notice the counter values changing, thereby proving that
pages are getting flushed to disk (if watching the physical file sizes
changing was not enough proof for you). 
 
Happy? 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
doller wrote:
<blockquote
cite="mid1126172338.931270.122790@.o13g2000cwo.googlegroups.com"
type="cite">
<pre wrap="">Ok,I kept my thought with me .But i think udont have a clue about all
that/.
Now if u understand about temp table then prove how sql server writes a
temp table to disk.
Prove it else i will think u dont have a clue about anything.
form
doler
Mike Hodgson wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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: <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>
doller wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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:
</pre>
<blockquote type="cite">
<pre wrap="">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
</pre>
</blockquote>
<pre wrap="">>from a UDF, because you can only do that using a table variable. So
</pre>
<blockquote type="cite">
<pre wrap="">that would be a functionality reason more so than a performance reason.
Aaron Bertrand
<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=<http://www.microsoft.com/communities/mvp/mvpdetails.mspx?Params=~CMTYDataSvcParams%5e~arg+Name%3d%22guid%22+Value%3d%22cf9c9e5b-abcf-4495-925b-0cd515fcdc89%22%2f%5e~sParams%5e~%2fsParams%5e~%2fCMTYDataSvcParams%5e></a>">http://www.microsoft.com/communities/mvp/mvpdetails.mspx?Params=%7eCMTYDataSvcParams%5e%7earg+Name%3d%22guid%22+Value%3d%22cf9c9e5b-abcf-4495-925b-0cd515fcdc89%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParams%5e"><http://www.microsoft.com/communities/mvp/mvpdetails.mspx?Params=%7eCMTYDataSvcParams%5e%7earg+Name%3d%22guid%22+Value%3d%22cf9c9e5b-abcf-4495-925b-0cd515fcdc89%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParams%5e></a>
(SQL MVP) has a great website called ASPFAQ <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=<http://www.aspfaq.com></a>">http://www.aspfaq.com"><http://www.aspfaq.com></a> 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?
<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=<http://www.aspfaq.com/show.asp?id=2475></a>">http://www.aspfaq.com/show.asp?id=2475"><http://www.aspfaq.com/show.asp?id=2475></a>
--
*mike hodgson*
blog: <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>
Yu6454 wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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" <<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mike.hodgson@.mallesons.nospam.com</a>
<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com"><mailto:mike.hodgson@.mallesons.nospam.com></a>> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl">news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl</a>...
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: <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>
Yu6454 wrote:
</pre>
<blockquote type="cite">
<pre wrap="">are temp tables written to disk? or are they cached like the database is
cached on queries.
</pre>
</blockquote>
</blockquote>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
</body>
</html>
--060804010504030403090104--|||Can you elaborate when a temp table that is created in a batch is actually
cleaned up;
that is, what is the definition of a session?
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:egdOrgMtFHA.616@.TK2MSFTNGP11.phx.gbl...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:e3iEXFItFHA.304@.TK2MSFTNGP11.phx.gbl...
> >> 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.
> <quibble>
> Temp tables created in stored procedures are cleaned up when the procedure
> ends, but temp tables created in batches are not.
> A temp table created in a batch lasts until it is dropped or the session
> ends. And can be referenced in stored procedures through the magic of
> deferred name resolution.
>
> create procedure GetFoo
> as
> select * from #foo
> go
> --fails
> exec GetFoo
> go
> create table #foo(i int)
> go
> insert into #foo(i) values (1)
> go
> --succeeds
> exec GetFoo
> </quibble>
>
> David
>|||A connection.
--
Nik Marshall-Blank MCSD/MCDBA
"nwbby" <noway@.yardd.com> wrote in message
news:OukkauStFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Can you elaborate when a temp table that is created in a batch is actually
> cleaned up;
> that is, what is the definition of a session?
>
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:egdOrgMtFHA.616@.TK2MSFTNGP11.phx.gbl...
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message
>> news:e3iEXFItFHA.304@.TK2MSFTNGP11.phx.gbl...
>> >> 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.
>> <quibble>
>> Temp tables created in stored procedures are cleaned up when the
>> procedure
>> ends, but temp tables created in batches are not.
>> A temp table created in a batch lasts until it is dropped or the session
>> ends. And can be referenced in stored procedures through the magic of
>> deferred name resolution.
>>
>> create procedure GetFoo
>> as
>> select * from #foo
>> go
>> --fails
>> exec GetFoo
>> go
>> create table #foo(i int)
>> go
>> insert into #foo(i) values (1)
>> go
>> --succeeds
>> exec GetFoo
>> </quibble>
>>
>> David
>>
>|||So, if this "connection" is always connected like for example a ASP.NET web
page via connection pooliing, the session is generally always there as
typically the connection is reused anyway? is this right?
And if the connection is generally reused, the session never ends and thus,
perhaps, the temp tables never get cleaned up and hence can be written to
disk, right?
"Nik Marshall-Blank" <Nik@.Operatelecom.com> wrote in message
news:GLdUe.277188$Jd.49604@.fe11.news.easynews.com...
> A connection.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "nwbby" <noway@.yardd.com> wrote in message
> news:OukkauStFHA.3236@.TK2MSFTNGP14.phx.gbl...
> > Can you elaborate when a temp table that is created in a batch is
actually
> > cleaned up;
> > that is, what is the definition of a session?
> >
> >
> >
> >
> > "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> > message news:egdOrgMtFHA.616@.TK2MSFTNGP11.phx.gbl...
> >>
> >> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> > message
> >> news:e3iEXFItFHA.304@.TK2MSFTNGP11.phx.gbl...
> >> >> 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.
> >>
> >> <quibble>
> >> Temp tables created in stored procedures are cleaned up when the
> >> procedure
> >> ends, but temp tables created in batches are not.
> >> A temp table created in a batch lasts until it is dropped or the
session
> >> ends. And can be referenced in stored procedures through the magic of
> >> deferred name resolution.
> >>
> >>
> >> create procedure GetFoo
> >> as
> >> select * from #foo
> >> go
> >> --fails
> >> exec GetFoo
> >> go
> >> create table #foo(i int)
> >> go
> >> insert into #foo(i) values (1)
> >> go
> >> --succeeds
> >> exec GetFoo
> >>
> >> </quibble>
> >>
> >>
> >> David
> >>
> >>
> >
> >
>|||I see where you're coming from and I think this is something I might
investigate.
--
Nik Marshall-Blank MCSD/MCDBA
"nwbby" <noway@.yardd.com> wrote in message
news:uHsJFzStFHA.3264@.TK2MSFTNGP12.phx.gbl...
> So, if this "connection" is always connected like for example a ASP.NET
> web
> page via connection pooliing, the session is generally always there as
> typically the connection is reused anyway? is this right?
> And if the connection is generally reused, the session never ends and
> thus,
> perhaps, the temp tables never get cleaned up and hence can be written to
> disk, right?
>
>
> "Nik Marshall-Blank" <Nik@.Operatelecom.com> wrote in message
> news:GLdUe.277188$Jd.49604@.fe11.news.easynews.com...
>> A connection.
>> --
>> Nik Marshall-Blank MCSD/MCDBA
>> "nwbby" <noway@.yardd.com> wrote in message
>> news:OukkauStFHA.3236@.TK2MSFTNGP14.phx.gbl...
>> > Can you elaborate when a temp table that is created in a batch is
> actually
>> > cleaned up;
>> > that is, what is the definition of a session?
>> >
>> >
>> >
>> >
>> > "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> > message news:egdOrgMtFHA.616@.TK2MSFTNGP11.phx.gbl...
>> >>
>> >> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> > message
>> >> news:e3iEXFItFHA.304@.TK2MSFTNGP11.phx.gbl...
>> >> >> 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.
>> >>
>> >> <quibble>
>> >> Temp tables created in stored procedures are cleaned up when the
>> >> procedure
>> >> ends, but temp tables created in batches are not.
>> >> A temp table created in a batch lasts until it is dropped or the
> session
>> >> ends. And can be referenced in stored procedures through the magic of
>> >> deferred name resolution.
>> >>
>> >>
>> >> create procedure GetFoo
>> >> as
>> >> select * from #foo
>> >> go
>> >> --fails
>> >> exec GetFoo
>> >> go
>> >> create table #foo(i int)
>> >> go
>> >> insert into #foo(i) values (1)
>> >> go
>> >> --succeeds
>> >> exec GetFoo
>> >>
>> >> </quibble>
>> >>
>> >>
>> >> David
>> >>
>> >>
>> >
>> >
>>
>|||Found this about MTS but it hints that pooling may fill up TempDB
http://msdn.microsoft.com/msdnmag/issues/0400/Serving/
--
Nik Marshall-Blank MCSD/MCDBA
"nwbby" <noway@.yardd.com> wrote in message
news:uHsJFzStFHA.3264@.TK2MSFTNGP12.phx.gbl...
> So, if this "connection" is always connected like for example a ASP.NET
> web
> page via connection pooliing, the session is generally always there as
> typically the connection is reused anyway? is this right?
> And if the connection is generally reused, the session never ends and
> thus,
> perhaps, the temp tables never get cleaned up and hence can be written to
> disk, right?
>
>
> "Nik Marshall-Blank" <Nik@.Operatelecom.com> wrote in message
> news:GLdUe.277188$Jd.49604@.fe11.news.easynews.com...
>> A connection.
>> --
>> Nik Marshall-Blank MCSD/MCDBA
>> "nwbby" <noway@.yardd.com> wrote in message
>> news:OukkauStFHA.3236@.TK2MSFTNGP14.phx.gbl...
>> > Can you elaborate when a temp table that is created in a batch is
> actually
>> > cleaned up;
>> > that is, what is the definition of a session?
>> >
>> >
>> >
>> >
>> > "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> > message news:egdOrgMtFHA.616@.TK2MSFTNGP11.phx.gbl...
>> >>
>> >> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> > message
>> >> news:e3iEXFItFHA.304@.TK2MSFTNGP11.phx.gbl...
>> >> >> 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.
>> >>
>> >> <quibble>
>> >> Temp tables created in stored procedures are cleaned up when the
>> >> procedure
>> >> ends, but temp tables created in batches are not.
>> >> A temp table created in a batch lasts until it is dropped or the
> session
>> >> ends. And can be referenced in stored procedures through the magic of
>> >> deferred name resolution.
>> >>
>> >>
>> >> create procedure GetFoo
>> >> as
>> >> select * from #foo
>> >> go
>> >> --fails
>> >> exec GetFoo
>> >> go
>> >> create table #foo(i int)
>> >> go
>> >> insert into #foo(i) values (1)
>> >> go
>> >> --succeeds
>> >> exec GetFoo
>> >>
>> >> </quibble>
>> >>
>> >>
>> >> David
>> >>
>> >>
>> >
>> >
>>
>|||In connection pooling each time the application closes the connection it
sends an sp_resetconnection to SQL Server. This cleans up any temp tables
and any environment changes that may have occurred during the most recent
connection. That connection then is able to be reused by someone else.
--
Andrew J. Kelly SQL MVP
"nwbby" <noway@.yardd.com> wrote in message
news:uHsJFzStFHA.3264@.TK2MSFTNGP12.phx.gbl...
> So, if this "connection" is always connected like for example a ASP.NET
> web
> page via connection pooliing, the session is generally always there as
> typically the connection is reused anyway? is this right?
> And if the connection is generally reused, the session never ends and
> thus,
> perhaps, the temp tables never get cleaned up and hence can be written to
> disk, right?
>
>
> "Nik Marshall-Blank" <Nik@.Operatelecom.com> wrote in message
> news:GLdUe.277188$Jd.49604@.fe11.news.easynews.com...
>> A connection.
>> --
>> Nik Marshall-Blank MCSD/MCDBA
>> "nwbby" <noway@.yardd.com> wrote in message
>> news:OukkauStFHA.3236@.TK2MSFTNGP14.phx.gbl...
>> > Can you elaborate when a temp table that is created in a batch is
> actually
>> > cleaned up;
>> > that is, what is the definition of a session?
>> >
>> >
>> >
>> >
>> > "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> > message news:egdOrgMtFHA.616@.TK2MSFTNGP11.phx.gbl...
>> >>
>> >> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> > message
>> >> news:e3iEXFItFHA.304@.TK2MSFTNGP11.phx.gbl...
>> >> >> 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.
>> >>
>> >> <quibble>
>> >> Temp tables created in stored procedures are cleaned up when the
>> >> procedure
>> >> ends, but temp tables created in batches are not.
>> >> A temp table created in a batch lasts until it is dropped or the
> session
>> >> ends. And can be referenced in stored procedures through the magic of
>> >> deferred name resolution.
>> >>
>> >>
>> >> create procedure GetFoo
>> >> as
>> >> select * from #foo
>> >> go
>> >> --fails
>> >> exec GetFoo
>> >> go
>> >> create table #foo(i int)
>> >> go
>> >> insert into #foo(i) values (1)
>> >> go
>> >> --succeeds
>> >> exec GetFoo
>> >>
>> >> </quibble>
>> >>
>> >>
>> >> David
>> >>
>> >>
>> >
>> >
>>
>|||Andrew,
Thanks for the information.
--
Nik Marshall-Blank MCSD/MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uj1SpBUtFHA.1204@.TK2MSFTNGP15.phx.gbl...
> In connection pooling each time the application closes the connection it
> sends an sp_resetconnection to SQL Server. This cleans up any temp tables
> and any environment changes that may have occurred during the most recent
> connection. That connection then is able to be reused by someone else.
> --
> Andrew J. Kelly SQL MVP
>
> "nwbby" <noway@.yardd.com> wrote in message
> news:uHsJFzStFHA.3264@.TK2MSFTNGP12.phx.gbl...
>> So, if this "connection" is always connected like for example a ASP.NET
>> web
>> page via connection pooliing, the session is generally always there as
>> typically the connection is reused anyway? is this right?
>> And if the connection is generally reused, the session never ends and
>> thus,
>> perhaps, the temp tables never get cleaned up and hence can be written to
>> disk, right?
>>
>>
>> "Nik Marshall-Blank" <Nik@.Operatelecom.com> wrote in message
>> news:GLdUe.277188$Jd.49604@.fe11.news.easynews.com...
>> A connection.
>> --
>> Nik Marshall-Blank MCSD/MCDBA
>> "nwbby" <noway@.yardd.com> wrote in message
>> news:OukkauStFHA.3236@.TK2MSFTNGP14.phx.gbl...
>> > Can you elaborate when a temp table that is created in a batch is
>> actually
>> > cleaned up;
>> > that is, what is the definition of a session?
>> >
>> >
>> >
>> >
>> > "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> > message news:egdOrgMtFHA.616@.TK2MSFTNGP11.phx.gbl...
>> >>
>> >> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> > message
>> >> news:e3iEXFItFHA.304@.TK2MSFTNGP11.phx.gbl...
>> >> >> 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.
>> >>
>> >> <quibble>
>> >> Temp tables created in stored procedures are cleaned up when the
>> >> procedure
>> >> ends, but temp tables created in batches are not.
>> >> A temp table created in a batch lasts until it is dropped or the
>> session
>> >> ends. And can be referenced in stored procedures through the magic
>> >> of
>> >> deferred name resolution.
>> >>
>> >>
>> >> create procedure GetFoo
>> >> as
>> >> select * from #foo
>> >> go
>> >> --fails
>> >> exec GetFoo
>> >> go
>> >> create table #foo(i int)
>> >> go
>> >> insert into #foo(i) values (1)
>> >> go
>> >> --succeeds
>> >> exec GetFoo
>> >>
>> >> </quibble>
>> >>
>> >>
>> >> David
>> >>
>> >>
>> >
>> >
>>
>>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0009_01C5B5FE.833338E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
So, based upon this link from the BOL you have below:
> Writing a page to disk does not imply removing it from memory. > Have a read of this BOL page: =http://msdn.microsoft.com/library/en-us/architec/8_ar_sa_8unn.asp.
It seems that the writes to the DISK from temp tables CAN occur at ANY =time when temp tables are used in a stored procedure. Would this be =valid statement?
Thus, if I were to use a table variable, and because table variables =clean up after themselves better than temp tables, table varibles (if =they aren't so large to exceed the available RAM) will have less chance =of being written to disk? is this correct or not?
Essentially table variables clean up after themselves better than temp =tables and because of that, table variables have less chance of being =written to disk in a web application that uses connection pooling. Would =this be a correct statement or not?
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:e6CVJaPtFHA.3864@.TK2MSFTNGP12.phx.gbl...
No.
Firstly I never said anything about some particular size after which =data gets written to disk (the OP had referenced a Microsoft FAQ article =that mentioned table variables using disk after reaching a certain size, =but I mentioned no such thing). However, if you check out BOL where it =talks about automatic checkpoints (eg. =http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_8y3y.asp) =you'll see that SQL Server issues a checkpoint (and therefore flushes =all dirty pages to disk) when the number of log records reaches the =number it estimates it can process in the recovery interval (or 70% full =if the DB is in simple recovery mode, if that is less). So it's sort of =true that there is a "size" after which data gets written to disk =(although not in the sense that you're hinting at).
Secondly, all SQL client connections interact with data in memory. =That data in memory (the cache) is backed by data on disk. When you =created your temp table with 200,000 rows in it, SQL Server wrote that =data to memory. The next time a checkpoint occurred (or the worker =threads happened to get around to it) that dirty data would have been =flushed to disk. But flushing data to disk does not remove it from the =cache (unless it has not been referenced for a long time in which case =the page then gets places in the buffer free list). SQL Server caches =all data that is accessed from the disk. Sometimes, due to memory =pressure, that data must be removed from the cache but that doesn't =negate the fact that the cache is a component that sits between the disk =and the client. The client cannot read data directly from the disk - it =always goes through RAM. If the client is trying to read pages that are =not in cache (known as a "hard" page fault) then SQL Server reads those =pages from disk, caches them in memory and returns the data to the =client. The fact that SQL Server returned your 200,000 rows very =quickly doesn't prove the data was on the disk and it doesn't prove the =data was not on the disk. All it proves is that the data was still in =cache at the time you queried it and there hadn't been enough memory =pressure yet to force SQL Server to swap those pages out of cache.
Also note that a checkpoint does not remove the dirty page from the =cache. It writes the page to disk and changes its dirty flag thereby =indicating that it is no longer dirty. But the page remains in memory. =Writing a page to disk does not imply removing it from memory. Have a =read of this BOL page: =http://msdn.microsoft.com/library/en-us/architec/8_ar_sa_8unn.asp.
--
mike hodgson
blog: http://sqlnerd.blogspot.com=20
doller wrote: By ur comments ur are just talking about disk I/O.
and file growth.U mentioned in ur comment that after a certain size of
data
it is written on disk.So what is that size when the data is written on
disk.As i know the database issues a checkpoint and after that only it
writes the data from buffer to disk and frees the buffer.
No, the checkpoint writes the dirty page to disk but does not remove =it from the cache.
I had created a temp table with more then 200000 rows (it should be
written in disk as u say )after creating the table i excuted some more
commands.
but i did not closed the connection so the temp table is still there.
after some time i executed a DML command it come in few secs.
all the 200000 rows.
If they are written on disk they will never come in the time i got the
records before.
So It proves the table structure and the data both are in buffer cache.
I wouldn't say it proves that but they most probably are in cache.
They are never written on disk.
No. It does not prove they were never written to disk. It only =proves that they are still in the cache. A page can be in the cache and =also on disk at the same time.
I am not answer to the question posted i am answering to u.
from
Doller
Mike Hodgson wrote:
doller wrote:
I dont think about u becasue i dont have to think about u.
That's good - I'm actually relieved you don't think about me. It'd be a
bit freaky if you did.
If they are written on disk and ur commiting ur records then why after
some time that table is deleted automatically.
Because it goes out of scope and is dropped automatically. (See the
Temporary Tables section in this BOL reference:
http://msdn.microsoft.com/library/en-us/architec/8_ar_da_4ucz.asp)
that is not really a proof that they are on disk.
The fact that the physical files backing the tempdb database grow during
the population of temp tables is indeed proof that the data is getting
written to disk.
So if ur are proving ur self then do only a test.
just create a temp table from QA and close the QA now execute any DML
command on that table.
if u get the record then u prove ur self.
As soon as you close the client connection the temp table will
automatically get dropped (as per the definition of a temp table). The
fact that you can't then query that non-existent temp table is not proof
that it never got written to disk. It just proves that it got dropped
before you tried to query it. By your reasoning, if you create a
permanent table
create table foo (bar varchar(10));
insert some data in it
insert into foo (bar) values ('a');
insert into foo (bar) values ('b');
insert into foo (bar) values ('c');
drop it
drop table foo;
and then try to query it
select * from foo;
the fact that you get an invalid object name error means that foo never
got written to disk. That's ludicrous.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com

--=_NextPart_000_0009_01C5B5FE.833338E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

So, based upon this link from the BOL you have below:
> Writing a page to disk does not imply =removing it from memory.
> Have a read of this BOL page:
It seems that the writes to the DISK from =temp tables CAN occur at ANY time when temp tables are used in a stored procedure. =Would this be valid statement?
Thus, if I were to use a table variable, and =because table variables clean up after themselves better than temp tables, table =varibles (if they aren't so large to exceed the available RAM) will have less chance =of being written to disk? is this correct or not?
Essentially table variables clean up after =themselves better than temp tables and because of that, table variables have less =chance of being written to disk in a web application that uses connection pooling. =Would this be a correct statement or not?
"Mike Hodgson" wrote in message news:e6CVJaPtFHA.3864=@.TK2MSFTNGP12.phx.gbl...No.Firstly I never said anything about some particular size after which data gets =written to disk (the OP had referenced a Microsoft FAQ article that mentioned =table variables using disk after reaching a certain size, but I mentioned no =such thing). However, if you check out BOL where it talks about =automatic checkpoints (eg. http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_8y3y.asp=) you'll see that SQL Server issues a checkpoint (and therefore flushes =all dirty pages to disk) when the number of log records reaches the number =it estimates it can process in the recovery interval (or 70% full if the =DB is in simple recovery mode, if that is less). So it's sort of true =that there is a "size" after which data gets written to disk (although not in the =sense that you're hinting at).Secondly, all SQL client connections =interact with data in memory. That data in memory (the cache) is backed =by data on disk. When you created your temp table with 200,000 rows in =it, SQL Server wrote that data to memory. The next time a checkpoint =occurred (or the worker threads happened to get around to it) that dirty data =would have been flushed to disk. But flushing data to disk does not =remove it from the cache (unless it has not been referenced for a long time in =which case the page then gets places in the buffer free list). SQL =Server caches all data that is accessed from the disk. Sometimes, due =to memory pressure, that data must be removed from the cache but that doesn't =negate the fact that the cache is a component that sits between the disk and the client. The client cannot read data directly from the disk - it =always goes through RAM. If the client is trying to read pages that are =not in cache (known as a "hard" page fault) then SQL Server reads those pages =from disk, caches them in memory and returns the data to the client. =The fact that SQL Server returned your 200,000 rows very quickly doesn't prove =the data was on the disk and it doesn't prove the data was not on the =disk. All it proves is that the data was still in cache at the time you queried =it and there hadn't been enough memory pressure yet to force SQL Server to =swap those pages out of cache.Also note that a checkpoint does not remove =the dirty page from the cache. It writes the page to disk and =changes its dirty flag thereby indicating that it is no longer dirty. But =the page remains in memory. Writing a page to disk does not imply =removing it from memory. Have a read of this BOL page:
--mike =hodgsonblog: http://sqlnerd.blogspot.com doller wrote: By ur comments ur are just talking about disk I/O.
and file growth.U mentioned in ur comment that after a certain size of
data
it is written on disk.So what is that size when the data is written on
disk.As i know the database issues a checkpoint and after that only it
writes the data from buffer to disk and frees the buffer.
No, the checkpoint writes the dirty page to =disk but does not remove it from the cache.
I had created a temp table with more then 200000 rows (it =should be
written in disk as u say )after creating the table i excuted some more
commands.
but i did not closed the connection so the temp table is still there.
after some time i executed a DML command it come in few secs.
all the 200000 rows.
If they are written on disk they will never come in the time i got the
records before.
So It proves the table structure and the data both are in buffer cache.
I wouldn't say it proves that but they most =probably are in cache.
They are never written on disk.
No. It does not prove they were never =written to disk. It only proves that they are still in the cache. =A page can be in the cache and also on disk at the same time.
I am not answer to the question posted i am answering to u.
from
Doller
Mike Hodgson wrote:
doller wrote:
I dont think about u =becasue i dont have to think about u.
That's good - I'm actually =relieved you don't think about me. It'd be a
bit freaky if you did.
If they are written on =disk and ur commiting ur records then why after
some time that table is deleted automatically.
Because it goes out of scope and =is dropped automatically. (See the
Temporary Tables section in this BOL reference:
http://msdn.microsoft.com/library/en-us/architec/8_ar_da_4ucz.asp)
that is not really a =proof that they are on disk.
The fact that the physical files =backing the tempdb database grow during
the population of temp tables is indeed proof that the data is getting
written to disk.
So if ur are proving ur =self then do only a test.
just create a temp table from QA and close the QA now execute any DML
command on that table.
if u get the record then u prove ur self.
As soon as you close the client =connection the temp table will
automatically get dropped (as per the definition of a temp table). The
fact that you can't then query that non-existent temp table is not proof
that it never got written to disk. It just proves that it got dropped
before you tried to query it. By your reasoning, if you create a
permanent table
create table foo (bar varchar(10));
insert some data in it
insert into foo (bar) values ('a');
insert into foo (bar) values ('b');
insert into foo (bar) values ('c');
drop it
drop table foo;
and then try to query it
select * from foo;
the fact that you get an invalid object name error means that foo never
got written to disk. That's ludicrous.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com
--=_NextPart_000_0009_01C5B5FE.833338E0--|||nwbby, see inline
> So, based upon this link from the BOL you have below:
> > Writing a page to disk does not imply removing it from memory.
> > Have a read of this BOL page: http://msdn.microsoft.com/library/en-us/architec/8_ar_sa_8unn.asp.
> It seems that the writes to the DISK from temp tables CAN occur at ANY time when temp tables are used in a stored procedure. Would
> this be valid statement?
Yes.
> Thus, if I were to use a table variable, and because table variables clean up after themselves better than temp tables, > table varibles (if
> they aren't so large to exceed the available RAM) will have less chance of being written to disk? is this correct or not?
IMO, no. If there is memory pressure, then both will suffer. Since a
temp table is designed as "just another table", I would expect a temp
table to better optimized for cache flushes. But is speculation on my
part. However, I can't think of any reason why memory pressure would not
affect table variables. The behavior might be different, but it will
hurt performance.
> Essentially table variables clean up after themselves better than temp tables and because of that, table variables have > less chance of
> being written to disk in a web application that uses connection pooling. Would this be a correct statement or not?
I don't like this generalization. If the programmer has left everything
up to garbage collection, then temp tables will tend to live longer. The
effect of this is not obvious, since the buffer manager uses statistics
on usage to determine which buffers to flush and which to keep (when
under memory pressure).
But of course, it is a bad practice to create a temp table and not close
at when done. Whenever I use a temp table, I close it at the end of the
batch. Otherwise, I use permanent tables.
I don't think memory behavior provides any reason to choose a table
variable over a temp table or vice versa. Other reasons are much more
compelling to choose one or the other.
Gert-Jan|||How do you close a temp table? And is there only one way to close a temp
table?
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:4323361C.F5109CA3@.toomuchspamalready.nl...
> nwbby, see inline
> > So, based upon this link from the BOL you have below:
> >
> > > Writing a page to disk does not imply removing it from memory.
> > > Have a read of this BOL page:
http://msdn.microsoft.com/library/en-us/architec/8_ar_sa_8unn.asp.
> >
> > It seems that the writes to the DISK from temp tables CAN occur at ANY
time when temp tables are used in a stored procedure. Would
> > this be valid statement?
> Yes.
> > Thus, if I were to use a table variable, and because table variables
clean up after themselves better than temp tables, > table varibles (if
> > they aren't so large to exceed the available RAM) will have less chance
of being written to disk? is this correct or not?
> IMO, no. If there is memory pressure, then both will suffer. Since a
> temp table is designed as "just another table", I would expect a temp
> table to better optimized for cache flushes. But is speculation on my
> part. However, I can't think of any reason why memory pressure would not
> affect table variables. The behavior might be different, but it will
> hurt performance.
> > Essentially table variables clean up after themselves better than temp
tables and because of that, table variables have > less chance of
> > being written to disk in a web application that uses connection pooling.
Would this be a correct statement or not?
> I don't like this generalization. If the programmer has left everything
> up to garbage collection, then temp tables will tend to live longer. The
> effect of this is not obvious, since the buffer manager uses statistics
> on usage to determine which buffers to flush and which to keep (when
> under memory pressure).
> But of course, it is a bad practice to create a temp table and not close
> at when done. Whenever I use a temp table, I close it at the end of the
> batch. Otherwise, I use permanent tables.
> I don't think memory behavior provides any reason to choose a table
> variable over a temp table or vice versa. Other reasons are much more
> compelling to choose one or the other.
> Gert-Jan|||see inline below...
> > Thus, if I were to use a table variable, and because table variables
clean up after themselves better than temp tables, > table varibles (if
> > they aren't so large to exceed the available RAM) will have less chance
of being written to disk? is this correct or not?
> IMO, no. If there is memory pressure, then both will suffer. Since a
> temp table is designed as "just another table", I would expect a temp
> table to better optimized for cache flushes. But is speculation on my
> part. However, I can't think of any reason why memory pressure would not
> affect table variables. The behavior might be different, but it will
> hurt performance.
But my statement above says, "If they aren't so large to exceed the
available RAM" means
"no memory pressure" So I want to take that out of the equation first. And
then add that a temp table, as you validated before can write to DISK at any
time.
> It seems that the writes to the DISK from temp tables CAN occur at
ANY time when temp tables
> are used in a stored procedure. Would this be valid statement?
Yes.
One could have a situation that temp table can write to disk with "no memory
pressure" and table variable do not write to disk "with no memory pressure".
Thus a compelling reason to use table variables over temp tables. Would this
be a reasonable statement or not?
> > Essentially table variables clean up after themselves better than temp
tables and because of that, table variables have > less chance of
> > being written to disk in a web application that uses connection pooling.
Would this be a correct statement or not?
> I don't like this generalization. If the programmer has left everything
> up to garbage collection, then temp tables will tend to live longer. The
> effect of this is not obvious, since the buffer manager uses statistics
> on usage to determine which buffers to flush and which to keep (when
> under memory pressure).
> But of course, it is a bad practice to create a temp table and not close
> at when done. Whenever I use a temp table, I close it at the end of the
> batch. Otherwise, I use permanent tables.
> I don't think memory behavior provides any reason to choose a table
> variable over a temp table or vice versa. Other reasons are much more
> compelling to choose one or the other.
> Gert-Jan|||Yu6454 wrote:
> How do you close a temp table?
Like any other table: DROP TABLE #MyTempTable
> And is there only one way to close a temp table?
Yes.
SQL-Server will also remove the temp table when the connection with
which it is associated is closed. But this is the user closing the
table, but SQL-Server collecting garbage.
Gert-Jan|||Yu6454 wrote:
> [snip]
> But my statement above says, "If they aren't so large to exceed the
> available RAM" means
> "no memory pressure" So I want to take that out of the equation first. And
> then add that a temp table, as you validated before can write to DISK at any
> time.
Your generic question "It seems that the writes to the DISK from temp
tables CAN occur at ANY time when temp tables are used in a stored
procedure." did not include the special circumstance where the temp
table is completely in memory, and that we have to assume no memory
pressure.
In that case, if the temp table is untouched, then no I/O will occur.
If rows are inserted, updated or deleted, SQL-Server will write to the
transaction log. If a checkpoint occurs, then all dirty pages are
written to disk. So if there have been changes to the temp table's data
and a checkpoint occurs, this will cause I/O.
I am not sure how all this works for table variables. The transaction
log will not be used for changes to the table variables' data, so that
would save I/O. I don't know if the table variables' data is written to
disk if a checkpoint occurs.
>[snip]
> One could have a situation that temp table can write to disk with "no memory
> pressure" and table variable do not write to disk "with no memory pressure".
> Thus a compelling reason to use table variables over temp tables. Would this
> be a reasonable statement or not?
The temp table part: yes. The table variable part: I don't know. Maybe.
But don't draw the wrong conclusions. If there is no memory pressure,
then I/O caused by background processes (such as the checkpoint process)
will not impact performance. I assume you only ask about I/O because of
potential impact on performance.
So you should really ask the following two questions:
1) How sure are you that there will never be memory pressure? If there
never is any memory pressure, and you are only using temp data, then why
use a database?
Let's suppose for one moment that at some point in time there is memory
pressure. Would it be acceptable that the system would grind to a halt?
If not, then you would want to know the answer to this question:
2) What happens if there is memory pressure? How will temp tables and
table variables behave under those circumstances?
Will the table variables' data be written to disk and flushed from
cache? Or will the table variables force other cached pages out of
memory? Is there a chance that the creation of a table variable will
fail because of insufficient memory?
I don't have the answers to this second question. Maybe other do...
Gert-Jan|||Ok. Another thing. If an insert, update, or delete occurs to the temp
table, you say a write occurs to the
transaction log. So if this transaction log in memory or DISK. I am pretty
sure that it is a DISK WRITE correct cause this the whole point of a
transaction log is to recover from a failures in the first place and that
can only reliably happen if it's on DISK correct?
> But don't draw the wrong conclusions. If there is no memory pressure,
> then I/O caused by background processes (such as the checkpoint process)
> will not impact performance. I assume you only ask about I/O because of
> potential impact on performance.
I have to disagree here on I/O on the disk as any I/O is going to impact
performance for the entire computer one way or another. If there is I/O,
and if there is a completely different sproc that uses the database for
whatever reason, like using a permanent table, it's going to have an effect
regardless. I say this from say a web application that can have many
different stored process that call upon the database.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:43236AFA.D7300CC3@.toomuchspamalready.nl...
> Yu6454 wrote:
> > [snip]
> > But my statement above says, "If they aren't so large to exceed the
> > available RAM" means
> > "no memory pressure" So I want to take that out of the equation first.
And
> > then add that a temp table, as you validated before can write to DISK at
any
> > time.
> Your generic question "It seems that the writes to the DISK from temp
> tables CAN occur at ANY time when temp tables are used in a stored
> procedure." did not include the special circumstance where the temp
> table is completely in memory, and that we have to assume no memory
> pressure.
> In that case, if the temp table is untouched, then no I/O will occur.
> If rows are inserted, updated or deleted, SQL-Server will write to the
> transaction log. If a checkpoint occurs, then all dirty pages are
> written to disk. So if there have been changes to the temp table's data
> and a checkpoint occurs, this will cause I/O.
> I am not sure how all this works for table variables. The transaction
> log will not be used for changes to the table variables' data, so that
> would save I/O. I don't know if the table variables' data is written to
> disk if a checkpoint occurs.
>
> >[snip]
> > One could have a situation that temp table can write to disk with "no
memory
> > pressure" and table variable do not write to disk "with no memory
pressure".
> > Thus a compelling reason to use table variables over temp tables. Would
this
> > be a reasonable statement or not?
> The temp table part: yes. The table variable part: I don't know. Maybe.
> But don't draw the wrong conclusions. If there is no memory pressure,
> then I/O caused by background processes (such as the checkpoint process)
> will not impact performance. I assume you only ask about I/O because of
> potential impact on performance.
> So you should really ask the following two questions:
> 1) How sure are you that there will never be memory pressure? If there
> never is any memory pressure, and you are only using temp data, then why
> use a database?
> Let's suppose for one moment that at some point in time there is memory
> pressure. Would it be acceptable that the system would grind to a halt?
> If not, then you would want to know the answer to this question:
> 2) What happens if there is memory pressure? How will temp tables and
> table variables behave under those circumstances?
> Will the table variables' data be written to disk and flushed from
> cache? Or will the table variables force other cached pages out of
> memory? Is there a chance that the creation of a table variable will
> fail because of insufficient memory?
> I don't have the answers to this second question. Maybe other do...
> Gert-Jan|||I think the transaction log IS used for table variables. Here's a snippit
from BOL:
a.. Transactions involving table variables last only for the duration of an
update on the table variable. Thus, table variables require less locking and
logging resources.
Since there is a transaction, it must be logged. If an error occurs during
the population of a table variable, the transaction surrounding the table
variable change operation must be rolled back. This transaction is separate
from any currently outstanding transaction (not nested), because changes
successfully written to a table variable survive a rollback--in other words,
if an insert, update or delete statement against a table variable completes,
then a subsequent rollback will not affect the contents of a table variable.
The main differences, as far as performance goes, between table variables
and temporary tables are that changes to temporary tables are part of an
outstanding transaction whereas changes to table variables are not, and that
you can create indexes (other than those implicitly created by primary key
or unique constraints) on temporary tables. Both are logged, and both can
migrate to tempdb because of memory pressure.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:43236AFA.D7300CC3@.toomuchspamalready.nl...
> Yu6454 wrote:
> > [snip]
> > But my statement above says, "If they aren't so large to exceed the
> > available RAM" means
> > "no memory pressure" So I want to take that out of the equation first.
And
> > then add that a temp table, as you validated before can write to DISK at
any
> > time.
> Your generic question "It seems that the writes to the DISK from temp
> tables CAN occur at ANY time when temp tables are used in a stored
> procedure." did not include the special circumstance where the temp
> table is completely in memory, and that we have to assume no memory
> pressure.
> In that case, if the temp table is untouched, then no I/O will occur.
> If rows are inserted, updated or deleted, SQL-Server will write to the
> transaction log. If a checkpoint occurs, then all dirty pages are
> written to disk. So if there have been changes to the temp table's data
> and a checkpoint occurs, this will cause I/O.
> I am not sure how all this works for table variables. The transaction
> log will not be used for changes to the table variables' data, so that
> would save I/O. I don't know if the table variables' data is written to
> disk if a checkpoint occurs.
>
> >[snip]
> > One could have a situation that temp table can write to disk with "no
memory
> > pressure" and table variable do not write to disk "with no memory
pressure".
> > Thus a compelling reason to use table variables over temp tables. Would
this
> > be a reasonable statement or not?
> The temp table part: yes. The table variable part: I don't know. Maybe.
> But don't draw the wrong conclusions. If there is no memory pressure,
> then I/O caused by background processes (such as the checkpoint process)
> will not impact performance. I assume you only ask about I/O because of
> potential impact on performance.
> So you should really ask the following two questions:
> 1) How sure are you that there will never be memory pressure? If there
> never is any memory pressure, and you are only using temp data, then why
> use a database?
> Let's suppose for one moment that at some point in time there is memory
> pressure. Would it be acceptable that the system would grind to a halt?
> If not, then you would want to know the answer to this question:
> 2) What happens if there is memory pressure? How will temp tables and
> table variables behave under those circumstances?
> Will the table variables' data be written to disk and flushed from
> cache? Or will the table variables force other cached pages out of
> memory? Is there a chance that the creation of a table variable will
> fail because of insufficient memory?
> I don't have the answers to this second question. Maybe other do...
> Gert-Jan|||Yes, you are right about the transaction behavior of table variables.
Thanks for the correction.
And I don't know how far all the theoretical stuff will lead. Even
without memory pressure inserts and deletes can (will?) cause I/O.
Below is the result of a test I did. Setup of this test: 110 MB
dedicated to SQL-Server (with physical memory reservation). The size of
dataset is 75 MB. Both with Table Variables and with Temp Tables, the
TempDB grows to 75MB.
Action | A. Table Variable | B. Temp Table
============================+=====================+=====================1. Insert rows | CPU + Writes + I/O | CPU + Writes + I/O
--+--+--
2. Update rows + checkpoint | CPU + Writes | CPU + Writes
--+--+--
3. Update rows | CPU | CPU
--+--+--
4. Delete rows | CPU + Writes + I/O | CPU + Writes + I/O
--+--+--
5. Checkpoint | Writes + little CPU | Writes + little CPU
I could measure no difference between the two.
The table's definition that I used was (id int, filler char(4444)). For
a regular table (and temp table), this requires one 8 KB page per row.
The fact that the table variable requires the same amount of disk space
as the temp table confirms that the same storage structures are used,
and that when it comes to memory consumptions, table variables are
equally efficient (not more efficient).
So the difference would only be in the logfile implications of
multi-statement transactions.
Gert-Jan
Brian Selzer wrote:
> I think the transaction log IS used for table variables. Here's a snippit
> from BOL:
> a.. Transactions involving table variables last only for the duration of an
> update on the table variable. Thus, table variables require less locking and
> logging resources.
> Since there is a transaction, it must be logged. If an error occurs during
> the population of a table variable, the transaction surrounding the table
> variable change operation must be rolled back. This transaction is separate
> from any currently outstanding transaction (not nested), because changes
> successfully written to a table variable survive a rollback--in other words,
> if an insert, update or delete statement against a table variable completes,
> then a subsequent rollback will not affect the contents of a table variable.
> The main differences, as far as performance goes, between table variables
> and temporary tables are that changes to temporary tables are part of an
> outstanding transaction whereas changes to table variables are not, and that
> you can create indexes (other than those implicitly created by primary key
> or unique constraints) on temporary tables. Both are logged, and both can
> migrate to tempdb because of memory pressure.
> [snip]|||So are the below tests all for transactions or not?
And how do you know if there is writes and/or I/O for each result you are
saying below?
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:43243C53.E2B83F19@.toomuchspamalready.nl...
> Yes, you are right about the transaction behavior of table variables.
> Thanks for the correction.
> And I don't know how far all the theoretical stuff will lead. Even
> without memory pressure inserts and deletes can (will?) cause I/O.
> Below is the result of a test I did. Setup of this test: 110 MB
> dedicated to SQL-Server (with physical memory reservation). The size of
> dataset is 75 MB. Both with Table Variables and with Temp Tables, the
> TempDB grows to 75MB.
> Action | A. Table Variable | B. Temp Table
> ============================+=====================+=====================> 1. Insert rows | CPU + Writes + I/O | CPU + Writes + I/O
> --+--+--
> 2. Update rows + checkpoint | CPU + Writes | CPU + Writes
> --+--+--
> 3. Update rows | CPU | CPU
> --+--+--
> 4. Delete rows | CPU + Writes + I/O | CPU + Writes + I/O
> --+--+--
> 5. Checkpoint | Writes + little CPU | Writes + little CPU
> I could measure no difference between the two.
> The table's definition that I used was (id int, filler char(4444)). For
> a regular table (and temp table), this requires one 8 KB page per row.
> The fact that the table variable requires the same amount of disk space
> as the temp table confirms that the same storage structures are used,
> and that when it comes to memory consumptions, table variables are
> equally efficient (not more efficient).
> So the difference would only be in the logfile implications of
> multi-statement transactions.
> Gert-Jan
>
> Brian Selzer wrote:
> >
> > I think the transaction log IS used for table variables. Here's a
snippit
> > from BOL:
> >
> > a.. Transactions involving table variables last only for the duration of
an
> > update on the table variable. Thus, table variables require less locking
and
> > logging resources.
> >
> > Since there is a transaction, it must be logged. If an error occurs
during
> > the population of a table variable, the transaction surrounding the
table
> > variable change operation must be rolled back. This transaction is
separate
> > from any currently outstanding transaction (not nested), because changes
> > successfully written to a table variable survive a rollback--in other
words,
> > if an insert, update or delete statement against a table variable
completes,
> > then a subsequent rollback will not affect the contents of a table
variable.
> >
> > The main differences, as far as performance goes, between table
variables
> > and temporary tables are that changes to temporary tables are part of an
> > outstanding transaction whereas changes to table variables are not, and
that
> > you can create indexes (other than those implicitly created by primary
key
> > or unique constraints) on temporary tables. Both are logged, and both
can
> > migrate to tempdb because of memory pressure.
> > [snip]|||see inline
Yu6454 wrote:
> So are the below tests all for transactions or not?
I don't understand the question.
The 5 actions mentioned in the experiment were in a batch, each action
(statement) separated with a WAITFOR DELAY '000:00:20' command. There
was no overall transaction, so basically, there were 6 transactions (1
insert, 2 update, 1 delete and 2 checkpoint).
> And how do you know if there is writes and/or I/O for each result you are
> saying below?
By using Windows' Performance Monitor to monitor the SQL Buffer Manager
Page Writes (for writes), Disk avg queue length (for I/O) and CPU (for
CPU). Because of each 20 second delay between the actions, it is easy to
separate the results.
Gert-Jan
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:43243C53.E2B83F19@.toomuchspamalready.nl...
> > Yes, you are right about the transaction behavior of table variables.
> > Thanks for the correction.
> >
> > And I don't know how far all the theoretical stuff will lead. Even
> > without memory pressure inserts and deletes can (will?) cause I/O.
> >
> > Below is the result of a test I did. Setup of this test: 110 MB
> > dedicated to SQL-Server (with physical memory reservation). The size of
> > dataset is 75 MB. Both with Table Variables and with Temp Tables, the
> > TempDB grows to 75MB.
> >
> > Action | A. Table Variable | B. Temp Table
> > ============================+=====================+=====================> > 1. Insert rows | CPU + Writes + I/O | CPU + Writes + I/O
> > --+--+--
> > 2. Update rows + checkpoint | CPU + Writes | CPU + Writes
> > --+--+--
> > 3. Update rows | CPU | CPU
> > --+--+--
> > 4. Delete rows | CPU + Writes + I/O | CPU + Writes + I/O
> > --+--+--
> > 5. Checkpoint | Writes + little CPU | Writes + little CPU
> >
> > I could measure no difference between the two.
> >
> > The table's definition that I used was (id int, filler char(4444)). For
> > a regular table (and temp table), this requires one 8 KB page per row.
> > The fact that the table variable requires the same amount of disk space
> > as the temp table confirms that the same storage structures are used,
> > and that when it comes to memory consumptions, table variables are
> > equally efficient (not more efficient).
> >
> > So the difference would only be in the logfile implications of
> > multi-statement transactions.
> >
> > Gert-Jan
[snip]|||Ok, I am still seeing writes to the DISK in your results shown below when
you are using table variables via inserts. Why would this be to begin with?
I thought this was supposed to be all memory if there is no memory pressure
for both table variables and temp tables, but now you have writes to disk in
both cases?
How much memory do you have anyway and is it free; and what kind of computer
are you running this on and is there anything else running on this computer?
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:43249A92.2F202546@.toomuchspamalready.nl...
> see inline
> Yu6454 wrote:
> >
> > So are the below tests all for transactions or not?
> I don't understand the question.
> The 5 actions mentioned in the experiment were in a batch, each action
> (statement) separated with a WAITFOR DELAY '000:00:20' command. There
> was no overall transaction, so basically, there were 6 transactions (1
> insert, 2 update, 1 delete and 2 checkpoint).
> > And how do you know if there is writes and/or I/O for each result you
are
> > saying below?
> By using Windows' Performance Monitor to monitor the SQL Buffer Manager
> Page Writes (for writes), Disk avg queue length (for I/O) and CPU (for
> CPU). Because of each 20 second delay between the actions, it is easy to
> separate the results.
> Gert-Jan
>
> > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > news:43243C53.E2B83F19@.toomuchspamalready.nl...
> > > Yes, you are right about the transaction behavior of table variables.
> > > Thanks for the correction.
> > >
> > > And I don't know how far all the theoretical stuff will lead. Even
> > > without memory pressure inserts and deletes can (will?) cause I/O.
> > >
> > > Below is the result of a test I did. Setup of this test: 110 MB
> > > dedicated to SQL-Server (with physical memory reservation). The size
of
> > > dataset is 75 MB. Both with Table Variables and with Temp Tables, the
> > > TempDB grows to 75MB.
> > >
> > > Action | A. Table Variable | B. Temp Table
> > >
============================+=====================+=====================> > > 1. Insert rows | CPU + Writes + I/O | CPU + Writes + I/O
> >
> --+--+--
> > > 2. Update rows + checkpoint | CPU + Writes | CPU + Writes
> >
> --+--+--
> > > 3. Update rows | CPU | CPU
> >
> --+--+--
> > > 4. Delete rows | CPU + Writes + I/O | CPU + Writes + I/O
> >
> --+--+--
> > > 5. Checkpoint | Writes + little CPU | Writes + little
CPU
> > >
> > > I could measure no difference between the two.
> > >
> > > The table's definition that I used was (id int, filler char(4444)).
For
> > > a regular table (and temp table), this requires one 8 KB page per row.
> > > The fact that the table variable requires the same amount of disk
space
> > > as the temp table confirms that the same storage structures are used,
> > > and that when it comes to memory consumptions, table variables are
> > > equally efficient (not more efficient).
> > >
> > > So the difference would only be in the logfile implications of
> > > multi-statement transactions.
> > >
> > > Gert-Jan
> [snip]|||How can you have Writes and no I/O for Checkpoint?
> > > > So the difference would only be in the logfile implications of
> > > > multi-statement transactions.
So based on the results that I see from your tests, there is no difference
between table variables and temp tables which seems "wrong" to begin with.
There has to be some difference as why would I want to use table variables
if the performance is exactly the same?
"Yu6454" <arks@.hotmail.com> wrote in message
news:%237dZ9uxtFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Ok, I am still seeing writes to the DISK in your results shown below when
> you are using table variables via inserts. Why would this be to begin
with?
> I thought this was supposed to be all memory if there is no memory
pressure
> for both table variables and temp tables, but now you have writes to disk
in
> both cases?
> How much memory do you have anyway and is it free; and what kind of
computer
> are you running this on and is there anything else running on this
computer?
>
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:43249A92.2F202546@.toomuchspamalready.nl...
> > see inline
> >
> > Yu6454 wrote:
> > >
> > > So are the below tests all for transactions or not?
> >
> > I don't understand the question.
> >
> > The 5 actions mentioned in the experiment were in a batch, each action
> > (statement) separated with a WAITFOR DELAY '000:00:20' command. There
> > was no overall transaction, so basically, there were 6 transactions (1
> > insert, 2 update, 1 delete and 2 checkpoint).
> >
> > > And how do you know if there is writes and/or I/O for each result
you
> are
> > > saying below?
> >
> > By using Windows' Performance Monitor to monitor the SQL Buffer Manager
> > Page Writes (for writes), Disk avg queue length (for I/O) and CPU (for
> > CPU). Because of each 20 second delay between the actions, it is easy to
> > separate the results.
> >
> > Gert-Jan
> >
> >
> > > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > > news:43243C53.E2B83F19@.toomuchspamalready.nl...
> > > > Yes, you are right about the transaction behavior of table
variables.
> > > > Thanks for the correction.
> > > >
> > > > And I don't know how far all the theoretical stuff will lead. Even
> > > > without memory pressure inserts and deletes can (will?) cause I/O.
> > > >
> > > > Below is the result of a test I did. Setup of this test: 110 MB
> > > > dedicated to SQL-Server (with physical memory reservation). The size
> of
> > > > dataset is 75 MB. Both with Table Variables and with Temp Tables,
the
> > > > TempDB grows to 75MB.
> > > >
> > > > Action | A. Table Variable | B. Temp Table
> > > >
> ============================+=====================+=====================> > > > 1. Insert rows | CPU + Writes + I/O | CPU + Writes +
I/O
> > >
> > --+--+--
> > > > 2. Update rows + checkpoint | CPU + Writes | CPU + Writes
> > >
> > --+--+--
> > > > 3. Update rows | CPU | CPU
> > >
> > --+--+--
> > > > 4. Delete rows | CPU + Writes + I/O | CPU + Writes +
I/O
> > >
> > --+--+--
> > > > 5. Checkpoint | Writes + little CPU | Writes + little
> CPU
> > > >
> > > > I could measure no difference between the two.
> > > >
> > > > The table's definition that I used was (id int, filler char(4444)).
> For
> > > > a regular table (and temp table), this requires one 8 KB page per
row.
> > > > The fact that the table variable requires the same amount of disk
> space
> > > > as the temp table confirms that the same storage structures are
used,
> > > > and that when it comes to memory consumptions, table variables are
> > > > equally efficient (not more efficient).
> > > >
> > > > So the difference would only be in the logfile implications of
> > > > multi-statement transactions.
> > > >
> > > > Gert-Jan
> > [snip]
>|||I believe that table variables are implemented under the covers as temporary
tables, but there are a few differences:
(1) Changes to table variables don't join in an outstanding transaction, so
they require fewer locking and logging resources.
(2) Fewer procedure recompiles are required if you use table variables
instead of temporary tables.
(3) Table variables exist only within the scope in which they are declared.
(4) Changes to table variables persist even if a rollback occurs.
(5) Only the implicit indexes that enforce a primary key or unique
constraint can exist on table variables.
The performance is not exactly the same. Determining which will provide
maximum performance is dependent upon whether a transaction is outstanding
when change operations occur and whether or not a non-unique index will
boost performance, among other things.
"Yu6454" <arks@.hotmail.com> wrote in message
news:ukiMpU6tFHA.3740@.TK2MSFTNGP14.phx.gbl...
> How can you have Writes and no I/O for Checkpoint?
> > > > > So the difference would only be in the logfile implications of
> > > > > multi-statement transactions.
> So based on the results that I see from your tests, there is no difference
> between table variables and temp tables which seems "wrong" to begin with.
> There has to be some difference as why would I want to use table variables
> if the performance is exactly the same?
>
> "Yu6454" <arks@.hotmail.com> wrote in message
> news:%237dZ9uxtFHA.2880@.TK2MSFTNGP12.phx.gbl...
> > Ok, I am still seeing writes to the DISK in your results shown below
when
> > you are using table variables via inserts. Why would this be to begin
> with?
> > I thought this was supposed to be all memory if there is no memory
> pressure
> > for both table variables and temp tables, but now you have writes to
disk
> in
> > both cases?
> >
> > How much memory do you have anyway and is it free; and what kind of
> computer
> > are you running this on and is there anything else running on this
> computer?
> >
> >
> >
> >
> > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > news:43249A92.2F202546@.toomuchspamalready.nl...
> > > see inline
> > >
> > > Yu6454 wrote:
> > > >
> > > > So are the below tests all for transactions or not?
> > >
> > > I don't understand the question.
> > >
> > > The 5 actions mentioned in the experiment were in a batch, each action
> > > (statement) separated with a WAITFOR DELAY '000:00:20' command. There
> > > was no overall transaction, so basically, there were 6 transactions (1
> > > insert, 2 update, 1 delete and 2 checkpoint).
> > >
> > > > And how do you know if there is writes and/or I/O for each result
> you
> > are
> > > > saying below?
> > >
> > > By using Windows' Performance Monitor to monitor the SQL Buffer
Manager
> > > Page Writes (for writes), Disk avg queue length (for I/O) and CPU (for
> > > CPU). Because of each 20 second delay between the actions, it is easy
to
> > > separate the results.
> > >
> > > Gert-Jan
> > >
> > >
> > > > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > > > news:43243C53.E2B83F19@.toomuchspamalready.nl...
> > > > > Yes, you are right about the transaction behavior of table
> variables.
> > > > > Thanks for the correction.
> > > > >
> > > > > And I don't know how far all the theoretical stuff will lead. Even
> > > > > without memory pressure inserts and deletes can (will?) cause I/O.
> > > > >
> > > > > Below is the result of a test I did. Setup of this test: 110 MB
> > > > > dedicated to SQL-Server (with physical memory reservation). The
size
> > of
> > > > > dataset is 75 MB. Both with Table Variables and with Temp Tables,
> the
> > > > > TempDB grows to 75MB.
> > > > >
> > > > > Action | A. Table Variable | B. Temp Table
> > > > >
> > ============================+=====================+=====================> > > > > 1. Insert rows | CPU + Writes + I/O | CPU + Writes +
> I/O
> > > >
> >
> --+--+--
> > > > > 2. Update rows + checkpoint | CPU + Writes | CPU + Writes
> > > >
> >
> --+--+--
> > > > > 3. Update rows | CPU | CPU
> > > >
> >
> --+--+--
> > > > > 4. Delete rows | CPU + Writes + I/O | CPU + Writes +
> I/O
> > > >
> >
> --+--+--
> > > > > 5. Checkpoint | Writes + little CPU | Writes +
little
> > CPU
> > > > >
> > > > > I could measure no difference between the two.
> > > > >
> > > > > The table's definition that I used was (id int, filler
char(4444)).
> > For
> > > > > a regular table (and temp table), this requires one 8 KB page per
> row.
> > > > > The fact that the table variable requires the same amount of disk
> > space
> > > > > as the temp table confirms that the same storage structures are
> used,
> > > > > and that when it comes to memory consumptions, table variables are
> > > > > equally efficient (not more efficient).
> > > > >
> > > > > So the difference would only be in the logfile implications of
> > > > > multi-statement transactions.
> > > > >
> > > > > Gert-Jan
> > > [snip]
> >
> >
>|||> So based on the results that I see from your tests, there is no difference
> between table variables and temp tables which seems "wrong" to begin with.
There are plenty of differences, and not all are around performance. In
many cases, the performance difference is negligible or 0. That doesn't
mean that's true in all cases. There are cases where a temp table will
outperform,
> There has to be some difference as why would I want to use table variables
> if the performance is exactly the same?
Why would I want to use SET vs. SELECT? Why would I want to use COALESCE
vs. ISNULL? Why would I want to use GETDATE vs. CURRENT_TIMESTAMP? Why
would I want to drive a Porsche vs. a Pathfinder? Why would I want to ride
a Harley vs. a Kawasaki? Why would I want to eat peanut butter vs. turkey?
Will any of these answers change based on the day of the week, or the
distance of the drive, or the project in question? Of course they will!
Performance is not the only criterium, but should be *part* of your testing.
There is no blanket statement that "temp tables are better" or "table
variables are better" because neither statement is true across all
situations. This is why the differences are presented to you, and nobody
worth their salt will say "always use x"; so you can make your own decision
in each scenario.|||Yu6454 wrote:
> Ok, I am still seeing writes to the DISK in your results shown below when
> you are using table variables via inserts. Why would this be to begin with?
Apparently the lazy writer writes new pages to disk immediately. Nobody
ever claimed that there wouldn't be any writes. The only proofs that
were provided proved that in some situations there were writes.
We (you, me, others) only theorized that they wouldn't be necessary.
Apparently, Microsoft deviated from this theoretical 'perfect' model,
possibly in favor of some other properties/behavior. I can imagine there
have been many design decisions, each with its own advantage and
disadvantage. Maybe the SQL-Server designers decided to secure and free
necessary disk space as soon as possible, making disk-less use of temp
tables/table variables impossible.
> I thought this was supposed to be all memory if there is no memory pressure
> for both table variables and temp tables, but now you have writes to disk in
> both cases?
I never made that claim. Nobody ever proved, guaranteed or claimed to be
sure that under those circumstances the operations would be writeless. I
(and you) have just been theorizing that they wouldn't be necessary in
order to function correctly.
> How much memory do you have anyway and is it free; and what kind of computer
> are you running this on and is there anything else running on this computer?
I ran the test many times. Believe me, the results were consistent. If
you don't believe me, then maybe it is time to start testing yourself.
Apparently SQL-Server doesn't work the way you would like it to work.
Yu6454 wrote in another post:
> How can you have Writes and no I/O for Checkpoint?
That's a good question. There could have been a few I/O that did not
show up in the disk queue length, but one would expect more than a few
I/O, especially since the amount of writes were comparable to the writes
witnessed for the insert action. I can only speculate. Maybe Windows (or
the device driver) defers updates of existing disk blocks. This may be
very specific for the laptop on which I tested this. Once SQL-Server has
issued a write, it is up to Windows to handle it.
> > > > > So the difference would only be in the logfile implications of
> > > > > multi-statement transactions.
> So based on the results that I see from your tests, there is no difference
> between table variables and temp tables which seems "wrong" to begin with.
The test doesn't cover a large transaction in which a table
variable/temp table is used extensively.
Also, you seem to forget (or are not willing to accept) that not every
technical difference translates into a significant performance
difference. One of the many design considerations is performance. Each
software layer tries to do its best, with the goal of the best possible
overall performance. If the query optimizer does a poor job for some
query, the CPU and CPU level cache might compensate some inefficiency.
Or the storage engine might compensate in a particular scenario, or the
disk subsystem might compensate. If all the layers and their techniques
'fail', only then will it translate into poor/poorer performance.
> There has to be some difference as why would I want to use table variables
> if the performance is exactly the same?
This has been my point from the beginning. In and of itself, table
variables do not perform better than temporary tables, with the
exception of maybe, just maybe, large transactions with extensive use of
tables variables. But even that doesn't guarantee better performance. I
haven't seen any research results posted in this newsgroup to that
effect.
Table variables are simply better suited for some situations, temp
tables better for other situations. There is no silver bullet. Others
have mentioned several good reasons to choose one or the other, such as
whether the table will be small or large, requires additional indexes or
not, is used in a UDF or needs to be used in multiple batches/stored
procedures, etc.
And the golden rule for performance is to test, test, test. A modern
RDBMS (such as SQL-Server) in combination with the specific hardware
configuration it's running on is simple too complex to predict which
techniques will provide the optimal performance.
Gert-Jan|||This is a multi-part message in MIME format.
--020802060609050501050507
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Hallelujah! Thank you Aaron.
This is essentially what I was trying to say towards the beginning of
this thread - that table variables & temp tables were designed for
different, but overlapping, purposes and there is no general rule of
thumb that table variables are always faster or temp tables are always
faster because of disk I/O or memory caching or...[fill in the blank(s)
here].
The take home message was, and still is, try different solutions to the
problem until your testing yields the one that, primarily, functions
correctly and, secondarily, performs the best.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Aaron Bertrand [SQL Server MVP] wrote:
>>So based on the results that I see from your tests, there is no difference
>>between table variables and temp tables which seems "wrong" to begin with.
>>
>There are plenty of differences, and not all are around performance. In
>many cases, the performance difference is negligible or 0. That doesn't
>mean that's true in all cases. There are cases where a temp table will
>outperform,
>
>>There has to be some difference as why would I want to use table variables
>>if the performance is exactly the same?
>>
>Why would I want to use SET vs. SELECT? Why would I want to use COALESCE
>vs. ISNULL? Why would I want to use GETDATE vs. CURRENT_TIMESTAMP? Why
>would I want to drive a Porsche vs. a Pathfinder? Why would I want to ride
>a Harley vs. a Kawasaki? Why would I want to eat peanut butter vs. turkey?
>Will any of these answers change based on the day of the week, or the
>distance of the drive, or the project in question? Of course they will!
>Performance is not the only criterium, but should be *part* of your testing.
>There is no blanket statement that "temp tables are better" or "table
>variables are better" because neither statement is true across all
>situations. This is why the differences are presented to you, and nobody
>worth their salt will say "always use x"; so you can make your own decision
>in each scenario.
>
>
--020802060609050501050507
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Hallelujah! Thank you Aaron. 
 
This is essentially what I was trying to say towards the beginning of
this thread - that table variables & temp tables were designed for
different, but overlapping, purposes and there is no general rule of
thumb that table variables are always faster or temp tables are always
faster because of disk I/O or memory caching or...[fill in the blank(s)
here]. 
 
The take home message was, and still is, try different solutions to the
problem until your testing yields the one that, primarily, functions
correctly and, secondarily, performs the best.</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Aaron Bertrand [SQL Server MVP] wrote:
<blockquote cite="miduHves06tFHA.1472@.TK2MSFTNGP15.phx.gbl" type="cite">
<blockquote type="cite">
<pre wrap="">So based on the results that I see from your tests, there is no difference
between table variables and temp tables which seems "wrong" to begin with.
</pre>
</blockquote>
<pre wrap="">
Why would I want to use SET vs. SELECT? Why would I want to use COALESCE
vs. ISNULL? Why would I want to use GETDATE vs. CURRENT_TIMESTAMP? Why
would I want to drive a Porsche vs. a Pathfinder? Why would I want to ride
a Harley vs. a Kawasaki? Why would I want to eat peanut butter vs. turkey?
Will any of these answers change based on the day of the week, or the
distance of the drive, or the project in question? Of course they will!
Performance is not the only criterium, but should be *part* of your testing.
There is no blanket statement that "temp tables are better" or "table
variables are better" because neither statement is true across all
situations. This is why the differences are presented to you, and nobody
worth their salt will say "always use x"; so you can make your own decision
in each scenario.
</pre>
</blockquote>
</body>
</html>
--020802060609050501050507--|||This is a multi-part message in MIME format.
--050804090603060301000301
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Yu6454 wrote:
>Ok. Another thing. If an insert, update, or delete occurs to the temp
>table, you say a write occurs to the
>transaction log. So if this transaction log in memory or DISK. I am pretty
>
It's written to RAM first and later flushed to disk (yes, even in the
transaction log). Transaction log records do not bypass memory.
>sure that it is a DISK WRITE correct cause this the whole point of a
>transaction log is to recover from a failures in the first place and that
>can only reliably happen if it's on DISK correct?
>
>>But don't draw the wrong conclusions. If there is no memory pressure,
>>then I/O caused by background processes (such as the checkpoint process)
>>will not impact performance. I assume you only ask about I/O because of
>>potential impact on performance.
>>
>I have to disagree here on I/O on the disk as any I/O is going to impact
>performance for the entire computer one way or another. If there is I/O,
>and if there is a completely different sproc that uses the database for
>whatever reason, like using a permanent table, it's going to have an effect
>regardless. I say this from say a web application that can have many
>different stored process that call upon the database.
>
The impact of disk I/Os can often be reduced until they are negligible
with techniques such as asynchronous reads, asynchronous writes and
read-aheads (all of which are employed by SQL Server). Technology
involving parallel data access paths can make a huge different too such
as different types of RAID configurations, multiple disk controllers,
multiple HBAs, disk write cache, etc. Also, since recently used data is
cached in RAM, it's entirely possible, probable even, that the other
stored procedure calls are working with data in memory (especially
frequently accessed data), and since memory & disk are two completely
separate subsystems, I/O in one subsystem does not impact I/O the other
unless they're being accessed serially (eg. a process reading a page
from disk into memory and then working with the in-memory copy of it).
As such, it's entirely feasible to suggest that asynchronous writes to
disk, in most cases, will not have any significant impact on overall SQL
Server performance.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
--050804090603060301000301
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Yu6454 wrote:
<blockquote cite="midepUiXlotFHA.3628@.TK2MSFTNGP14.phx.gbl" type="cite">
<pre wrap="">Ok. Another thing. If an insert, update, or delete occurs to the temp
table, you say a write occurs to the
transaction log. So if this transaction log in memory or DISK. I am pretty
</pre>
</blockquote>
<tt>It's written to RAM first and later flushed to disk (yes, even in
the transaction log). Transaction log records do not bypass memory.</tt> 
<blockquote cite="midepUiXlotFHA.3628@.TK2MSFTNGP14.phx.gbl" type="cite">
<pre wrap="">sure that it is a DISK WRITE correct cause this the whole point of a
transaction log is to recover from a failures in the first place and that
can only reliably happen if it's on DISK correct?
</pre>
<blockquote type="cite">
<pre wrap="">But don't draw the wrong conclusions. If there is no memory pressure,
then I/O caused by background processes (such as the checkpoint process)
will not impact performance. I assume you only ask about I/O because of
potential impact on performance.
</pre>
</blockquote>
<pre wrap="">I have to disagree here on I/O on the disk as any I/O is going to impact
performance for the entire computer one way or another. If there is I/O,
and if there is a completely different sproc that uses the database for
whatever reason, like using a permanent table, it's going to have an effect
regardless. I say this from say a web application that can have many
different stored process that call upon the database.
</pre>
</blockquote>
<tt>The impact of disk I/Os can often be reduced until they are
negligible with techniques such as asynchronous reads, asynchronous
writes and read-aheads (all of which are employed by SQL Server).
Technology involving parallel data access paths can make a huge
different too such as different types of RAID configurations, multiple
disk controllers, multiple HBAs, disk write cache, etc. Also, since
recently used data is cached in RAM, it's entirely possible, probable
even, that the other stored procedure calls are working with data in
memory (especially frequently accessed data), and since memory &
disk are two completely separate subsystems, I/O in one subsystem does
not impact I/O the other unless they're being accessed serially (eg. a
process reading a page from disk into memory and then working with the
in-memory copy of it). As such, it's entirely feasible to suggest that
asynchronous writes to disk, in most cases, will not have any
significant impact on overall SQL Server performance.</tt> 
<tt></tt>
<div class="moz-signature">
-- 
 mike hodgson 
 blog: <a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>
</div>
<blockquote cite="midepUiXlotFHA.3628@.TK2MSFTNGP14.phx.gbl" type="cite">
<div class="moz-signature">
 
</div>
<pre wrap=""></pre>
</blockquote>
</body>
</html>
--050804090603060301000301--|||This is a multi-part message in MIME format.
--=_NextPart_000_0225_01C5B7EF.DD9A2260
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
It's written to RAM first and later flushed to disk (yes, even in the =transaction log). Transaction log records do not bypass memory.
I just want to clarify that the process used to write to the database is =not the same as the process used to write to the transaction log. There =is a buffer cache for data pages and a log cache for log records. SQL =Server ensures that log records are flushed to disk before any =corresponding data pages. SQL Server also turns on the =FILE_FLAG_WRITE_THROUGH option to bypass the hardware write-back cache =for writes to the transaction log to ensure that the log records =actually make it to the physical media before any corresponding dirty =data pages. In addition, when a commit occurs, all pending log records =are flushed to disk immediately. If you're really concerned about =performance, use a caching RAID controller with a battery backup and =enable write caching on the controller.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:ut$FK4$tFHA.3068@.TK2MSFTNGP14.phx.gbl...
Yu6454 wrote: Ok. Another thing. If an insert, update, or delete occurs to the temp
table, you say a write occurs to the
transaction log. So if this transaction log in memory or DISK. I am =pretty
It's written to RAM first and later flushed to disk (yes, even in the =transaction log). Transaction log records do not bypass memory.
sure that it is a DISK WRITE correct cause this the whole point of a
transaction log is to recover from a failures in the first place and =that
can only reliably happen if it's on DISK correct?
But don't draw the wrong conclusions. If there is no memory pressure,
then I/O caused by background processes (such as the checkpoint process)
will not impact performance. I assume you only ask about I/O because of
potential impact on performance.
I have to disagree here on I/O on the disk as any I/O is going to =impact
performance for the entire computer one way or another. If there is =I/O,
and if there is a completely different sproc that uses the database for
whatever reason, like using a permanent table, it's going to have an =effect
regardless. I say this from say a web application that can have many
different stored process that call upon the database.
The impact of disk I/Os can often be reduced until they are negligible =with techniques such as asynchronous reads, asynchronous writes and =read-aheads (all of which are employed by SQL Server). Technology =involving parallel data access paths can make a huge different too such =as different types of RAID configurations, multiple disk controllers, =multiple HBAs, disk write cache, etc. Also, since recently used data is =cached in RAM, it's entirely possible, probable even, that the other =stored procedure calls are working with data in memory (especially =frequently accessed data), and since memory & disk are two completely =separate subsystems, I/O in one subsystem does not impact I/O the other =unless they're being accessed serially (eg. a process reading a page =from disk into memory and then working with the in-memory copy of it). =As such, it's entirely feasible to suggest that asynchronous writes to =disk, in most cases, will not have any significant impact on overall SQL =Server performance.
--
mike hodgson
blog: http://sqlnerd.blogspot.com
--=_NextPart_000_0225_01C5B7EF.DD9A2260
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

It's written to RAM first and later flushed to disk (yes, even in the transaction =log). Transaction log records do not bypass memory.
I just want to clarify that the process =used to write to the database is not the same as the process used to write =to the transaction log. There is a buffer cache for data pages and a log =cache for log records. SQL Server ensures that log records are flushed =to disk before any corresponding data pages. SQL Server also turns on the FILE_FLAG_WRITE_THROUGH option to =bypass the hardware write-back cache for writes to the transaction log to ensure =that the log records actually make it to the physical media before any =corresponding dirty data pages. In addition, when a commit occurs, all =pending log records are flushed to disk immediately. If you're really =concerned about performance, use a caching RAID controller with a =battery backup and enable write caching on the controller.
"Mike Hodgson" wrote in message news:ut$FK4$tFHA.3068=@.TK2MSFTNGP14.phx.gbl...Yu6454 wrote: Ok. Another thing. If an insert, update, =or delete occurs to the temp
table, you say a write occurs to the
transaction log. So if this transaction log in memory or DISK. I am =pretty
It's written to RAM first and later flushed to =disk (yes, even in the transaction log). Transaction log records do =not bypass memory.
sure that it is a DISK WRITE correct cause =this the whole point of a
transaction log is to recover from a failures in the first place and =that
can only reliably happen if it's on DISK correct?
But don't draw the wrong =conclusions. If there is no memory pressure,
then I/O caused by background processes (such as the checkpoint process)
will not impact performance. I assume you only ask about I/O because of
potential impact on performance.
I have to disagree here on I/O on =the disk as any I/O is going to impact
performance for the entire computer one way or another. If there is =I/O,
and if there is a completely different sproc that uses the database for
whatever reason, like using a permanent table, it's going to have an =effect
regardless. I say this from say a web application that can have many
different stored process that call upon the database.
The impact of disk I/Os can often be reduced =until they are negligible with techniques such as asynchronous reads, =asynchronous writes and read-aheads (all of which are employed by SQL =Server). Technology involving parallel data access paths can make a huge =different too such as different types of RAID configurations, multiple disk =controllers, multiple HBAs, disk write cache, etc. Also, since recently used =data is cached in RAM, it's entirely possible, probable even, that the other =stored procedure calls are working with data in memory (especially frequently = accessed data), and since memory & disk are two completely =separate subsystems, I/O in one subsystem does not impact I/O the other unless =they're being accessed serially (eg. a process reading a page from disk into =memory and then working with the in-memory copy of it). As such, it's =entirely feasible to suggest that asynchronous writes to disk, in most cases, =will not have any significant impact on overall SQL Server performance.
--mike hodgsonblog: http://sqlnerd.blogspot.com
--=_NextPart_000_0225_01C5B7EF.DD9A2260--|||> There are plenty of differences, and not all are around performance. In
> many cases, the performance difference is negligible or 0. That doesn't
> mean that's true in all cases. There are cases where a temp table will
> outperform,
Can you name me *several* cases that occur in the REAL world on a consistent
basis where a temp table will outperform a table variable?
Is seems like the case where a temp table will outperform a table variable
will be far and few between. And if it did, the store procedure would be
re-written anyway as I would *think* that such a case would be some batch or
stored procedure that took hours to do anyway and that it would be addressed
in other ways like i.e. more memory, re-examine the stored procedure, etc.
It seems like the table variable will outperform or at least equal temp
table in a vast and large majority of real world scenarios than temp tables
as I keep hearing that there are cases, but do these "cases" are far and few
between or if they do they are rarely used when compared to other cases, ie.
"REPORTING". And as we speak, memory gets cheaper and cheaper; thus, table
variables can be used on larger and larger datasets. This advancement of
technology in memory and less financial costs for gigabytes of RAM further
minimize the cases where temp tables appear to have some advantage over
table variables on large datasets.
> > There has to be some difference as why would I want to use table
variables
> > if the performance is exactly the same?
> Why would I want to use SET vs. SELECT? Why would I want to use COALESCE
> vs. ISNULL? Why would I want to use GETDATE vs. CURRENT_TIMESTAMP? Why
> would I want to drive a Porsche vs. a Pathfinder? Why would I want to
ride
> a Harley vs. a Kawasaki? Why would I want to eat peanut butter vs.
turkey?
> Will any of these answers change based on the day of the week, or the
> distance of the drive, or the project in question? Of course they will!
> Performance is not the only criterium, but should be *part* of your
testing.
> There is no blanket statement that "temp tables are better" or "table
> variables are better" because neither statement is true across all
> situations. This is why the differences are presented to you, and nobody
> worth their salt will say "always use x"; so you can make your own
decision
> in each scenario.
>|||> Can you name me *several* cases that occur in the REAL world on a
> consistent
> basis where a temp table will outperform a table variable?
> Is seems like the case where a temp table will outperform a table variable
> will be far and few between.
Fine, believe what you want. Use table variables always, and never perform
any performance testing when your parameters change.
Conversation over.

Thursday, February 9, 2012

are temp tables written to disk?

No comments:

Post a Comment

sql nulls slow

Blog Archive

About Me

About Me

Followers

Blog Archive