query database table: tempdb

Showing posts with label tempdb. Show all posts

Wednesday, March 21, 2012

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 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: 
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn2.microsoft.com/en-us/library/ms175527.aspx</a> ">http://msdn2.microsoft.com/en-us/library/ms175527.aspx">http://msdn2.microsoft.com/en-us/library/ms175527.aspx</a> 
but most of that information is already contained in the document
Linchi pointed you to. 
 
</tt><tt>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. </tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
<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?
> >
> >
> > >
>

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?
>

Monday, March 19, 2012

How to recover from dropped tempdb?

I am testing sql server 2005 in different disaster situations happened under 2000. I was reading a lot about no update on system tables, so:

sp_detach_db 'tempdb'

In sql 2000 i could issue something like the following command to recover:

use master

sp_configure 'allow updates', 1

reconfigure with override

insert sys.sysdatabases (name, dbid, sid, mode, status, status2, crdate, reserved, category, cmptlevel, filename, version)

values ('tempdb', 2, 0x01, 0, 8, 1090520064, '2007-01-27 13:03:10.873', '1900-01-01 00:00:00.000', 0, '90', 'D:\beep\mssql\temp\tempdb.mdf', 611)

sp_configure 'allow updates', 0

reconfigure with override

As tempdb is hardcoded db id 2, I do not have a chance to recover without a master backup.

Of course, this situation can be recovered using a master backup, but it is not always available.

Thank you for your comments.

How is it that you are detaching tempdb?

1> sp_detach_db tempdb
2> go
Msg 7940, Level 16, State 1:
System databases master, model, msdb, and tempdb cannot be detached.
1>

|||

Thanks for the fast response.

Yes, it is the normal behavior. But imagine the following situation:

The end-user tries to move the system databases to another disk. He reads the information on your site (http://support.microsoft.com/kb/224071/) and starts the server with /T3608. He executes the procedure step by step, moves msdb and model, forgets to read the remaining part and happy to move tempdb the same way.

This is the point, when you are getting involved. How to proceed?

|||Well, that script won'y work with SQL Server 2005. You can not allowed to make direct updates to system tables. You can't drop or detach a system database, so the only way of having an issue with tempdb is that it either becomes damaged while SQL Server is running in which case the SQL Server will go offline and fixing the issue is a matter of restarting SQL Server or you have an issue while starting up SQL Server in which case the SQL Server won't start and you would then need to go through the process documented in BOL for recreating tempdb.|||I have asked that the article be updated to remove SQL 2005 from the section that does the moves via detach. ALTER DATABASE should be used to change the file paths in these cases and there are detailed instructions in books online.|||

Thank you for your comment.

You _can_ detach the tempdb, if you start SQL Server 2005 with the /T3608 parameter [I have tried before posting the question]. From the point tempdb detached it cannot be re-attached, nor can be re-created using the way you mention, as the row with hardcoded database id "2" does not exists in the sysdatabases table.

|||

Thank you.

For closing the issue can we say, that the only way to recover from this situation is attaching a clean master db and re-attach the databases and re-create all global settings (logins, endpoints, etc.) stored in master?

|||

If you have a backup of master, you could restore that after putting in the clean master. Master is usually small enough that it should not be too big a burden to do full backups on a regular basis.

And if you had truly followed the article, the first step is

Make a current backup of all databases, especially master, from their current location.

which should allow you to recover.

How to recover from dropped tempdb?

I am testing sql server 2005 in different disaster situations happened under 2000. I was reading a lot about no update on system tables, so:

sp_detach_db 'tempdb'

In sql 2000 i could issue something like the following command to recover:

use master

sp_configure 'allow updates', 1

reconfigure with override

insert sys.sysdatabases (name, dbid, sid, mode, status, status2, crdate, reserved, category, cmptlevel, filename, version)

values ('tempdb', 2, 0x01, 0, 8, 1090520064, '2007-01-27 13:03:10.873', '1900-01-01 00:00:00.000', 0, '90', 'D:\beep\mssql\temp\tempdb.mdf', 611)

sp_configure 'allow updates', 0

reconfigure with override

As tempdb is hardcoded db id 2, I do not have a chance to recover without a master backup.

Of course, this situation can be recovered using a master backup, but it is not always available.

Thank you for your comments.

How is it that you are detaching tempdb?

1> sp_detach_db tempdb
2> go
Msg 7940, Level 16, State 1:
System databases master, model, msdb, and tempdb cannot be detached.
1>

|||

Thanks for the fast response.

Yes, it is the normal behavior. But imagine the following situation:

This is the point, when you are getting involved. How to proceed?

Thank you for your comment.

|||

Thank you.

|||

If you have a backup of master, you could restore that after putting in the clean master. Master is usually small enough that it should not be too big a burden to do full backups on a regular basis.

And if you had truly followed the article, the first step is

Make a current backup of all databases, especially master, from their current location.

which should allow you to recover.

Wednesday, March 21, 2012

How to reduce tempdb use?

How to reduce tempdb use?

Monday, March 19, 2012

How to recover from dropped tempdb?

How to recover from dropped tempdb?

query database table

Blog Archive

About Me