473,705 Members | 5,871 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 3725

"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
6514
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). thx
10
13270
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 doesn't help.
2
3894
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
1666
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 working DBs 4. (1) SCSI HD that's not doing anything.
8
12774
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 temporary tables are created in tempDB and locking the entire Database. So at the same time if...
3
6675
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 tempdb tx log is only 200MB and there are over 50G disk space available.
3
7094
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 space avaible on my hard drive. Is there any way to solve that problem ? - Is it possible to limit...
2
12716
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 disk. Once it's grown to that size we can't seem to shrink it again short of restarting the server.
0
1758
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
8768
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 usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8690
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9274
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. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8979
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7895
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6606
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5933
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4440
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2491
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.