473,544 Members | 2,477 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 3707

"Warren Wright" <wa***********@ us.scorex.com> wrote in message
news:40******** *************@n ews.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******** *************@n ews.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
6507
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 (64 MB reserved for SQL Server). tempdb size is 10 MB. Currently i'm experiencing slow response for large queries and sometimes users have problems logging on the internet (authentication stops). ...
10
13258
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 database 'tempdb' is full. Back up the transaction log for the database to free up some log space. I have tried "dump transaction tempdb with no_log" right before I run the SQL command. But that...
2
3884
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 of available space on the disk. So I don't understand why this error occurred. Does anyone have any ideas? Thanks, Tom
1
1660
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. Processor: (1) AMD XP 2800 2. 1st HD (IDE 0) is the system & boot drive 3. (3) SCSI HD make up a hardware RAID level 0 (striped without parity)solution - these striped drives are just for my...
8
12747
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 created and after that values are inserted in these tables.But this Stored Procedure is called from Java Portal. When then Stored Procedure execute from Java end new transaction begins. So all these...
3
6670
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 prompts for the following error: "The log file for database "tempdb" is full. Back up the transaction log for the database to free up some log space." the problem is, at the time of error, the...
3
7070
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 reduce the size of the transaction log fil during an execution ? Indeed, I have a script inserting a very large quantity of data (many Go) and during that process my transaction log file use all the...
2
12705
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 help me here. I have a client who is using SQL 2005 (sorry, don't have the exact build with me). They run a weekly process which causes TempDB to grow to over 100GB before it fails due to a full...
0
1742
by: VIPS | last post by:
On Apr 3, 7:29 am, "Krisnamourt via SQLMonster.com" <u21487@uwe> wrote: REGARDING TEMP DB SIZE: -For a small db server that does about 10-20 GB of logging per day, I would recommend having a size that does not go through AUTO_GROW. i.e ensure that the size of tempdb is big enough that it will hold the whole days work.
0
7437
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7373
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7625
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7717
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5306
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3427
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3421
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1848
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
993
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.