472,338 Members | 1,680 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,338 software developers and data experts.

Is tempdb big enough for what I'm doing, plus other questions.

Hi All,

First, where can I get some questions of this sort answered?
Preferably, are there good books or online guides that I can consult for
these types of answers when necessary?

1. How do I know, when executing a query from Query Analyzer or
otherwise, how many temporary tables will be needed, and how big they
will be?
2. Where will those temporary tables be created? Always in tempdb?
3. If part of the estimated execution plan in Query Analyzer involves a
"Hash Match/Inner Join" with an estimated row count of 5 million, how
many tables will be created along the way?
4. What happens if tempdb doesn't have enough space to create a
temporary table in its entirety?
5. etc.

Those are just examples. I guess what I need is a good book on
query/script optimization, including selects, deletes, updates, etc,
that I can reference that will describe to me what is going on
internally when certain execution plans are executed.

Not only how to write optimized queries (use SARGs, etc), but why to
write a query one way over another.

Second, my current question regards an UPDATE I'm trying to get to run
in a reasonable amount of time.

It has to update 11 million rows of a 175 million row table. I did the
math, and with each record containing 220 bytes of data, a temporary
table of all the rows would take up 33+ gigs. If, by chance, such an
update creates a temporary table with the full 175 million rows, what
happens if the partition tempdb.mdf resides on is only 28 gig in size,
etc?

I hope that made some sense, and thanks.

Warren Wright
Dallas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #1
2 3619

"Warren Wright" <wa***********@us.scorex.com> wrote in message
news:40*********************@news.frii.net...
Hi All,

First, where can I get some questions of this sort answered?
Preferably, are there good books or online guides that I can consult for
these types of answers when necessary?

1. How do I know, when executing a query from Query Analyzer or
otherwise, how many temporary tables will be needed, and how big they
will be?
2. Where will those temporary tables be created? Always in tempdb?
3. If part of the estimated execution plan in Query Analyzer involves a
"Hash Match/Inner Join" with an estimated row count of 5 million, how
many tables will be created along the way?
4. What happens if tempdb doesn't have enough space to create a
temporary table in its entirety?
5. etc.

Those are just examples. I guess what I need is a good book on
query/script optimization, including selects, deletes, updates, etc,
that I can reference that will describe to me what is going on
internally when certain execution plans are executed.

Not only how to write optimized queries (use SARGs, etc), but why to
write a query one way over another.

Second, my current question regards an UPDATE I'm trying to get to run
in a reasonable amount of time.

It has to update 11 million rows of a 175 million row table. I did the
math, and with each record containing 220 bytes of data, a temporary
table of all the rows would take up 33+ gigs. If, by chance, such an
update creates a temporary table with the full 175 million rows, what
happens if the partition tempdb.mdf resides on is only 28 gig in size,
etc?

I hope that made some sense, and thanks.

Warren Wright
Dallas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


I assume from your description above that you're talking about working
tables used internally by MSSQL, and not temp tables created by users and
applications? I don't have any detailed answers, since this isn't documented
anywhere that I know of, with the exception of the space required in tempdb
for creating indexes - see "tempdb and Index Creation" in Books Online. The
best description of MSSQL internals is "Inside SQL Server 2000" by Kalen
Delaney - it may well have more information, but I don't have my copy to
hand, so I can't check.

If MSSQL needs to use disk space, it's usually in tempdb (unless you're
creating indexes - see the BOL section above), and if there isn't enough
space then tempdb will grow according to its autogrowth settings. If you run
out of disk space, you'll get the usual errors raised when this happens
(1105, from memory, but I may be wrong).

In your situation, if you have a large transaction and limited disk space,
then one possible solution is to execute the UPDATE in batches, and truncate
the log periodically as you do it. This is only possible if you don't need
to do your UPDATE inside a transaction, of course.

Simon
Jul 20 '05 #2

"Warren Wright" <wa***********@us.scorex.com> wrote in message
news:40*********************@news.frii.net...
Hi All,

First, where can I get some questions of this sort answered?
Preferably, are there good books or online guides that I can consult for
these types of answers when necessary?

1. How do I know, when executing a query from Query Analyzer or
otherwise, how many temporary tables will be needed, and how big they
will be?
2. Where will those temporary tables be created? Always in tempdb?
3. If part of the estimated execution plan in Query Analyzer involves a
"Hash Match/Inner Join" with an estimated row count of 5 million, how
many tables will be created along the way?
4. What happens if tempdb doesn't have enough space to create a
temporary table in its entirety?
5. etc.

Those are just examples. I guess what I need is a good book on
query/script optimization, including selects, deletes, updates, etc,
that I can reference that will describe to me what is going on
internally when certain execution plans are executed.

Not only how to write optimized queries (use SARGs, etc), but why to
write a query one way over another.

Second, my current question regards an UPDATE I'm trying to get to run
in a reasonable amount of time.

It has to update 11 million rows of a 175 million row table. I did the
math, and with each record containing 220 bytes of data, a temporary
table of all the rows would take up 33+ gigs. If, by chance, such an
update creates a temporary table with the full 175 million rows, what
happens if the partition tempdb.mdf resides on is only 28 gig in size,
etc?

I hope that made some sense, and thanks.

Warren Wright
Dallas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


I assume from your description above that you're talking about working
tables used internally by MSSQL, and not temp tables created by users and
applications? I don't have any detailed answers, since this isn't documented
anywhere that I know of, with the exception of the space required in tempdb
for creating indexes - see "tempdb and Index Creation" in Books Online. The
best description of MSSQL internals is "Inside SQL Server 2000" by Kalen
Delaney - it may well have more information, but I don't have my copy to
hand, so I can't check.

If MSSQL needs to use disk space, it's usually in tempdb (unless you're
creating indexes - see the BOL section above), and if there isn't enough
space then tempdb will grow according to its autogrowth settings. If you run
out of disk space, you'll get the usual errors raised when this happens
(1105, from memory, but I may be wrong).

In your situation, if you have a large transaction and limited disk space,
then one possible solution is to execute the UPDATE in batches, and truncate
the log periodically as you do it. This is only possible if you don't need
to do your UPDATE inside a transaction, of course.

Simon
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: New DB Admin | last post by:
Is it a good thing to do? what are the cons? Are there any risks? (this is an ISP database running 24 hrs) I have sql 6.5 on Win NT with 256 MB Ram...
10
by: Jay Chan | last post by:
I keep getting the following error message when I run a serie of SQL commands: Server: Msg 9002, Level 17, State 6, Line 15 The log file for...
2
by: Tom | last post by:
I received an error that the log in tempdb was full, but the log and data segments are set to automatically grow with no limit AND there is plenty...
1
by: adude | last post by:
I am interested to hear if people think it would be a good idea to move the Master & TempDB to a different HD. Here is my DB Server's set up: 1....
8
by: arijitchatterjee123 | last post by:
Hi Group, I am facing a problem regarding locking. I have created a Stored Procedure in my Database. In this Stored Procedure Temprary Tables get...
3
by: New MSSQL DBA | last post by:
has anyone met with this before? the setting is SQL2K with SP3 on a 2 node active-active W2K3 cluster. on one of the machine, it occasionally...
3
by: Kurt | last post by:
Hello I have questions about how works transaction log et the database tempdb in SQL Server and I hop you could help me - Is it possible to...
2
by: Thomas R. Hummel | last post by:
I was able to find a few posts on this topic, but none of them quite seemed to fit the situation, so I'm hoping that someone else might be able to...
0
by: VIPS | last post by:
On Apr 3, 7:29am, "Krisnamourt via SQLMonster.com" <u21487@uwe> wrote: REGARDING TEMP DB SIZE: -For a small db server that does about 10-20...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.