How to reduce tempdb use?

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/downl...gWithTempDB.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.
>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/downl...gWithTempDB.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:[vbcol=seagreen]
> It would be useful to get a good understanding of how tempdb is used by SQ
L
> 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/downl...gWithTempDB.doc
> Linchi
> "paulgyro@.gmail.com" wrote:
>|||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:
>
>
>|||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:[vbcol=seagreen]
> It would be useful to get a good understanding of how tempdb is used by SQ
L
> 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/downl...gWithTempDB.doc
> Linchi
> "paulgyro@.gmail.com" wrote:
>|||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:
>
>
>|||<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: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:[vbcol=seagreen]
> <paulgyro@.gmail.com> wrote in message
> news:1151514894.873943.289410@.i40g2000cwc.googlegroups.com...
> Indexing may help, but not really sure w/o seeing the queries.
> Incidentally why do you think the tempdb is being used to often?
>