How to reduce tempdb use?

I'm wondering if there are any general guidelines on how one can go
about reducing SQL Server's use to the tempdb?
Add RAM to the server? Index columns? I'm not sure how to go about
reducing the tempdb use on DB as I think it is being used too often.It would be useful to get a good understanding of how tempdb is used by SQL
Server before considering how to reduce the tempdb usage. Here's a good
Microsoft whitepaper on tempdb usage (primarily on SQL2005).
http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc
Linchi
"paulgyro@.gmail.com" wrote:
> I'm wondering if there are any general guidelines on how one can go
> about reducing SQL Server's use to the tempdb?
> Add RAM to the server? Index columns? I'm not sure how to go about
> reducing the tempdb use on DB as I think it is being used too often.
>|||Thanks for pointing me to this fine document. It provided additional
detail into what I already knew. I am fairly sure that the issue is
with the queries. Seeing I have no way to tune the specific queries
what other steps like I've suggested before could I take to reduce
tempdb use?
Paul
Linchi Shea wrote:
> It would be useful to get a good understanding of how tempdb is used by SQL
> Server before considering how to reduce the tempdb usage. Here's a good
> Microsoft whitepaper on tempdb usage (primarily on SQL2005).
> http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc
> Linchi
> "paulgyro@.gmail.com" wrote:
> > I'm wondering if there are any general guidelines on how one can go
> > about reducing SQL Server's use to the tempdb?
> >
> > Add RAM to the server? Index columns? I'm not sure how to go about
> > reducing the tempdb use on DB as I think it is being used too often.
> >
> >|||This is a multi-part message in MIME format.
--000900000304030902060701
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
If you cannot change the queries then there's not much you can do to
reduce the use of tempdb; it still gets used for the same things
regardless of how much memory is in your server, although the creation
of more useful indexes *may* reduce the optimiser's need to use tempdb
as much during query execution (better sort orders, covering indexes, etc.).
However, there are a few things you can do, if you have the resources,
to make tempdb use more efficient, like moving it onto its own disk,
splitting it into multiple files (1 per CPU or core is a good rule of
thumb, and they should all be equal in size), place tempdb (or its
parts) on more efficient disks or RAID arrays (RAID 1 or RAID 1/0), put
more spindles in the RAID array(s) on which it is located (ie. more
parallel disk operations), etc.
There is a little information in BOL about optimising tempdb performance:
http://msdn2.microsoft.com/en-us/library/ms175527.aspx
but most of that information is already contained in the document Linchi
pointed you to.
After thinking about it a little more, _if you had memory pressure_ then
adding more memory *might* make tempdb a little more efficient as it
wouldn't be quite so reliant on disk I/O. It would still have the same
level of activity but might not need to commit it to disk quite as often
(but that all depends on the frequency of commits and dirty page flushes
and stuff like that). However, I suspect you'd likely just be throwing
money away if tempdb was the only reason you were adding memory to your
SQL box.
--
*mike hodgson*
http://sqlnerd.blogspot.com
paulgyro@.gmail.com wrote:
>Thanks for pointing me to this fine document. It provided additional
>detail into what I already knew. I am fairly sure that the issue is
>with the queries. Seeing I have no way to tune the specific queries
>what other steps like I've suggested before could I take to reduce
>tempdb use?
>Paul
>
>Linchi Shea wrote:
>
>>It would be useful to get a good understanding of how tempdb is used by SQL
>>Server before considering how to reduce the tempdb usage. Here's a good
>>Microsoft whitepaper on tempdb usage (primarily on SQL2005).
>>http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc
>>Linchi
>>"paulgyro@.gmail.com" wrote:
>>
>>I'm wondering if there are any general guidelines on how one can go
>>about reducing SQL Server's use to the tempdb?
>>Add RAM to the server? Index columns? I'm not sure how to go about
>>reducing the tempdb use on DB as I think it is being used too often.
>>
>>
>
>
--000900000304030902060701
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>If you cannot change the queries then there's not much you can do
to reduce the use of tempdb; it still gets used for the same things
regardless of how much memory is in your server, although the creation
of more useful indexes <b>may</b> reduce the optimiser's need to use
tempdb as much during query execution (better sort orders, covering
indexes, etc.).<br>
<br>
However, there are a few things you can do, if you have the resources,
to make tempdb use more efficient, like moving it onto its own disk,
splitting it into multiple files (1 per CPU or core is a good rule of
thumb, and they should all be equal in size), place tempdb (or its
parts) on more efficient disks or RAID arrays (RAID 1 or RAID 1/0), put
more spindles in the RAID array(s) on which it is located (ie. more
parallel disk operations), etc.<br>
<br>
There is a little information in BOL about optimising tempdb
performance:<br>
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn2.microsoft.com/en-us/library/ms175527.aspx</a><br>">http://msdn2.microsoft.com/en-us/library/ms175527.aspx">http://msdn2.microsoft.com/en-us/library/ms175527.aspx</a><br>
but most of that information is already contained in the document
Linchi pointed you to.<br>
<br>
</tt><tt>After thinking about it a little more, <u>if you had memory
pressure</u> then adding more memory <b>might</b> make tempdb a little
more efficient as it wouldn't be quite so reliant on disk I/O. It
would still have the same level of activity but might not need to
commit it to disk quite as often (but that all depends on the frequency
of commits and dirty page flushes and stuff like that). However, I
suspect you'd likely just be throwing money away if tempdb was the only
reason you were adding memory to your SQL box. </tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:paulgyro@.gmail.com">paulgyro@.gmail.com</a> wrote:
<blockquote
cite="mid1151532819.832502.77880@.j72g2000cwa.googlegroups.com"
type="cite">
<pre wrap="">Thanks for pointing me to this fine document. It provided additional
detail into what I already knew. I am fairly sure that the issue is
with the queries. Seeing I have no way to tune the specific queries
what other steps like I've suggested before could I take to reduce
tempdb use?
Paul
Linchi Shea wrote:
</pre>
<blockquote type="cite">
<pre wrap="">It would be useful to get a good understanding of how tempdb is used by SQL
Server before considering how to reduce the tempdb usage. Here's a good
Microsoft whitepaper on tempdb usage (primarily on SQL2005).
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc</a>">http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc">http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc</a>
Linchi
<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:paulgyro@.gmail.com">"paulgyro@.gmail.com"</a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I'm wondering if there are any general guidelines on how one can go
about reducing SQL Server's use to the tempdb?
Add RAM to the server? Index columns? I'm not sure how to go about
reducing the tempdb use on DB as I think it is being used too often.
</pre>
</blockquote>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--000900000304030902060701--|||<paulgyro@.gmail.com> wrote in message
news:1151514894.873943.289410@.i40g2000cwc.googlegroups.com...
> I'm wondering if there are any general guidelines on how one can go
> about reducing SQL Server's use to the tempdb?
> Add RAM to the server? Index columns? I'm not sure how to go about
> reducing the tempdb use on DB as I think it is being used too often.
Indexing may help, but not really sure w/o seeing the queries.
Incidentally why do you think the tempdb is being used to often?
>|||I understand that you would need to see the queries to know if
additional indexes would help. I am just looking for general info at
this point as I've never had to trouble shoot tempdb usage before.
We see the tempdb on our 40 gig SQL Server 2000 DB grows quickly over
the course of 2 - 3 days to 10+ gig in size. It appears the product's
queries all contain order by's and many of the queries are accessing
millions of rows. Over a course of the 2- 3 days there comes a point
where the system is brought to it's knees and we need to kick all users
off and allow SQL Server at least 20 mins to recover. Recently we
require a reboot to get SQL Server useable again. Even restarting the
SQL Server process does not seen to fix that issue that a reboot does.
We have already moved the tempdb to it's own hard drive volume which
seemed to help just a little to reduce I/O contention but not
eliminating the core problem.
The product vendor doesn't know what to say other then it's a SQL
Server issue.
Greg D. Moore (Strider) wrote:
> <paulgyro@.gmail.com> wrote in message
> news:1151514894.873943.289410@.i40g2000cwc.googlegroups.com...
> > I'm wondering if there are any general guidelines on how one can go
> > about reducing SQL Server's use to the tempdb?
> >
> > Add RAM to the server? Index columns? I'm not sure how to go about
> > reducing the tempdb use on DB as I think it is being used too often.
> Indexing may help, but not really sure w/o seeing the queries.
> Incidentally why do you think the tempdb is being used to often?
>
> >|||<paulgyro@.gmail.com> wrote in message
news:1151615288.109125.246870@.d56g2000cwd.googlegroups.com...
> I understand that you would need to see the queries to know if
> additional indexes would help. I am just looking for general info at
> this point as I've never had to trouble shoot tempdb usage before.
>
I think you're barking up the wrong tree here. Really it sounds like the
application's fault.
However, one thing to check is the colation.
I've seen the tempdb grow out of control when joining tables from two
different databases that have a different collation.
Check your master, your tempdb and your application database(s).
> We see the tempdb on our 40 gig SQL Server 2000 DB grows quickly over
> the course of 2 - 3 days to 10+ gig in size. It appears the product's
> queries all contain order by's and many of the queries are accessing
> millions of rows. Over a course of the 2- 3 days there comes a point
> where the system is brought to it's knees and we need to kick all users
> off and allow SQL Server at least 20 mins to recover. Recently we
> require a reboot to get SQL Server useable again. Even restarting the
> SQL Server process does not seen to fix that issue that a reboot does.
> We have already moved the tempdb to it's own hard drive volume which
> seemed to help just a little to reduce I/O contention but not
> eliminating the core problem.
> The product vendor doesn't know what to say other then it's a SQL
> Server issue.
>
> Greg D. Moore (Strider) wrote:
> > <paulgyro@.gmail.com> wrote in message
> > news:1151514894.873943.289410@.i40g2000cwc.googlegroups.com...
> > > I'm wondering if there are any general guidelines on how one can go
> > > about reducing SQL Server's use to the tempdb?
> > >
> > > Add RAM to the server? Index columns? I'm not sure how to go about
> > > reducing the tempdb use on DB as I think it is being used too often.
> >
> > Indexing may help, but not really sure w/o seeing the queries.
> >
> > Incidentally why do you think the tempdb is being used to often?
> >
> >
> > >
>