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

[Warren Wright]

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!

[Simon Hayes]

"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

[Simon Hayes]

"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