TempDb crashed my server...

Question

TempDb crashed my server...

Charmer

SSChampion

Points: 14903
More actions
May 17, 2012 at 8:42 am

#256645

Hi friends,
I made a T-SQL statement to insert some records, when i execute this query, it make temp-db to get increased and keep on increasing until it finishes the execution(approximately from 28000 Kb to 4156890 Kb). So my sql server is slowed down.
Friends, What causes temp-db to get increased? (FYI i deal with 8 million records)
friends, give me valuable suggestions
Thanks,
Charmer

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

tempdb is used to store temporary tables and data related to sorting (amongst other things)

if your insert required some kind of sort then it's possible tempdb would be increasing - also the transaction log of your target database would also increase in size.

try making your insert statement smaller (less rows) and perform several inserts (say for example 1 million at a time)

you should be able to free up the space in the tempdb after the operation has completed using

use tempdb

go

dbcc shrinkfile(2,1)

dbcc shrinkfile(1,1)

MVDBA

dazarooney Newbie Points: 3 More actions · Answer 2

Yeah I've had that before and used that to fix it.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Before you ran the query, you ran "Display Estimated Execution Plan". How many rows were estimated for the insert?

If not, and if the source of the insert was a select, did you run the select alone to see how many rows would be inserted?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Charmer SSChampion Points: 14903 More actions · Answer 4

ChrisM@Work (5/17/2012)
Before you ran the query, you ran "Display Estimated Execution Plan". How many rows were estimated for the insert?
If not, and if the source of the insert was a select, did you run the select alone to see how many rows would be inserted?

yes, the source of the insert is a select having 5 million records...

Thanks,
Charmer

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Charmer (5/17/2012)
ChrisM@Work (5/17/2012)
Before you ran the query, you ran "Display Estimated Execution Plan". How many rows were estimated for the insert?
If not, and if the source of the insert was a select, did you run the select alone to see how many rows would be inserted?
yes, the source of the insert is a select having 5 million records...

It's quite likely that an insert of 5 million rows will require "4156890 Kb" tempdb if there's a sort in the plan somewhere. You might follow Michael's suggestion above, or leave tempdb at the new size if this is a recurring and frequent requirement.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Charmer SSChampion Points: 14903 More actions · Answer 6

ChrisM@Work (5/17/2012)
Charmer (5/17/2012)
ChrisM@Work (5/17/2012)
Before you ran the query, you ran "Display Estimated Execution Plan". How many rows were estimated for the insert?
If not, and if the source of the insert was a select, did you run the select alone to see how many rows would be inserted?
yes, the source of the insert is a select having 5 million records...
It's quite likely that an insert of 5 million rows will require "4156890 Kb" tempdb if there's a sort in the plan somewhere. You might follow Michael's suggestion above, or leave tempdb at the new size if this is a recurring and frequent requirement.

Chris, i have attached my execution plan here...

Thanks,
Charmer

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Do you really need to do all those LTRIM(RTRIM()) function calls in your join criteria? That is one thing that is potentially killing your query.

Charmer SSChampion Points: 14903 More actions · Answer 8

Lynn Pettis (5/17/2012)
Do you really need to do all those LTRIM(RTRIM()) function calls in your join criteria? That is one thing that is potentially killing your query.

yes Lynn, i have experienced it once...but without trim function, it is not giving the exact row count...what to do?

Thanks,
Charmer

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

michael vessey (5/17/2012)
you should be able to free up the space in the tempdb after the operation has completed using
use tempdb
go
dbcc shrinkfile(2,1)
dbcc shrinkfile(1,1)

Please do not suggest that people shrink tempDB, it is documented (there is a kb article) to potentially cause corruption that will require a SQL Server restart to resolve.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Truth? Clean up the data. If the data has leading and trailing spaces and these need to be removed for a query, perhaps retaining them in the data is wrong to begin with.

But before we go down that path, we also need to see the CREATE TABLE statements for the tables including the indexes on each.

Looking at the query in the estimated execution plan, I can't see how it will use any indexes. This is probably why your tempdb database is exploding when this query runs.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 11

GilaMonster (5/17/2012)
michael vessey (5/17/2012)
you should be able to free up the space in the tempdb after the operation has completed using
use tempdb
go
dbcc shrinkfile(2,1)
dbcc shrinkfile(1,1)
Please do not suggest that people shrink tempDB, it is documented (there is a kb article) to potentially cause corruption that will require a SQL Server restart to resolve.

please feel free to post a link to that article, i've not seen it, but happy to take it on board. - i have found that sometimes you JUST NEED to shrink it - ie case of emergency where you cannot just "shut down" your server. - remember most servers have more than one database - shutting your payroll system down mid "pay-run" just because your sales database did something stupid would be unpleasant!!! - get yourself back up and running and THEN when it's safe restrat and rebuild tempdb.

i can also point you at the following microsoft article that specifies that syntax (option 2 and 3 in the article)

http://support.microsoft.com/kb/307487

i've used this syntax many many times after huge manual operations and never in 14 years had a single corruption from it...

MVDBA

Charmer SSChampion Points: 14903 More actions · Answer 12

michael vessey (5/18/2012)
GilaMonster (5/17/2012)
michael vessey (5/17/2012)
you should be able to free up the space in the tempdb after the operation has completed using
use tempdb
go
dbcc shrinkfile(2,1)
dbcc shrinkfile(1,1)
Please do not suggest that people shrink tempDB, it is documented (there is a kb article) to potentially cause corruption that will require a SQL Server restart to resolve.
please feel free to post a link to that article, i've not seen it, but happy to take it on board. - i have found that sometimes you JUST NEED to shrink it - ie case of emergency where you cannot just "shut down" your server. - remember most servers have more than one database - shutting your payroll system down mid "pay-run" just because your sales database did something stupid would be unpleasant!!! - get yourself back up and running and THEN when it's safe restrat and rebuild tempdb.
i can also point you at the following microsoft article that specifies that syntax (option 2 and 3 in the article)
http://support.microsoft.com/kb/307487
i've used this syntax many many times after huge manual operations and never in 14 years had a single corruption from it...

they said that the database is connected to something like SAN disk...i have no knowledge of what it is....so they say if i shrink the database, it would cause some problem.

Thanks,
Charmer

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

michael vessey (5/18/2012)
please feel free to post a link to that article,

It's the kb article you just referenced.

From that kb:

IMPORTANT: You must run DBCC SHRINKFILE command while no other activity occurs in the tempdb database. To make sure that other processes cannot use tempdb while DBCC SHRINKFILE executes, you must restart SQL Server in the single user mode.

and

If tempdb is in use and you attempt to shrink it by using the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands, you may receive multiple consistency errors

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass