SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question regarding TempDB


Question regarding TempDB

Author
Message
kramaswamy
kramaswamy
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1527 Visits: 1803
Hi all,

I have a question regarding the way TempDB works.

Currently, I have a database which is on a drive that contains ~130GB of space. The database is taking ~60GB, and will continue to grow daily.

TempDB is also sitting on the same drive (not the system drive BTW). TempDB is currently taking ~60GB.

This is becoming an issue, as eventually the drive will run out of space even if TempDB remains the same size it is currently.

What is the best way of addressing this problem? As far as I see it, I have two solutions:

1) Stop and restart the server. This will reset TempDB to its initial size. However, will this just result in a problem arriving again in the future when TempDB grows back to 60GB? If I understand TempDB correctly, it never shrinks in size - if an operation requires a large amount of TempDB space, TempDB will grow accordingly to fit the operation, but once the operation is complete, it will remain at this size. Therefore, eventually that same operation will run again, and set the size of TempDB back to 60GB again. If I choose this solution, then I will need to schedule regular restarts of the server.

1) a) On that note, is that a solution which is commonly employed? Scheduling regular restarts of the server, that is.


2) Buy a new drive and move either TempDB or the database to that drive. Of course, eventually this will result in the same problem, but presumably it will take much longer to arrive.

3) Optimize all the queries so that TempDB isn't taxed as much, by removing blocking operations. Of course this is probably the ideal solution, but its also the one that is least feasable, since it requires a much greater effort.


It seems to me like if a new drive is not available, option 1) is the only real solution. Anyone else have any ideas?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88294 Visits: 45277
1 is not a solution. It's a mitigation, nothing more and it's not a particularly good one. No, it is not a commonly employed 'solution' to restart SQL often to reset TempDB. In fact, you should have TempDB's starting size set to the size it normally reaches so that you don't have the overhead of growing tempDB after a restart.

Your solutions are 2 and 3. Add more drive space and optimise the queries to use less TempDB. Yes, it's work (and it pays very well)

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


GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23767 Visits: 9730
I'll second the motion on 2 & 3.

A good addition to 2 would be to move tempdb onto a separate RAID array from the main production database. If you can do that you can often get a significant performance boost on the whole server. Works even better if it's on a separate I/O channel, of course. But the main thing is, instead of replacing your current 130 Gb array, add another one and move one or the other database onto it while leaving the other one behind.

(Yes, I realize I'm assuming you have these on RAID arrays instead of single drives, for redundancy and all that. Thought I'd better phrase it all that way, just in case it makes a difference in your solution.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
kramaswamy
kramaswamy
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1527 Visits: 1803
That's pretty much what I thought. Alright, thanks, I'll present the two ideas to my superiors and see which one they go for.
Vegard Hagen
Vegard Hagen
SSChasing Mays
SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)

Group: General Forum Members
Points: 617 Visits: 387
For best results, if you're running a busy production server, you should place TempDB on a seperate drive and split it up into one file for each CPU. That way, each scheduler has its own TempDB file to work with, which can greatly speed things up when running parallel operations (a disk will always be slow compared to a CPU, but at least if they're not all constantly asking for the same file...;-)) All TempDB files should be equally sized and have the same autogrowth settings.



Vegard Hagen
Norwegian DBA, blogger and generally a nice guy who believes the world is big enough for all of us.

@vegard_hagen on Twitter
Blog: Vegard's corner (No actual SQL stuff here - haven't found my niche yet. Maybe some day...)

"It is better to light a candle than to curse the darkness."
(Chinese proverb)


GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88294 Visits: 45277
http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx

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


Vegard Hagen
Vegard Hagen
SSChasing Mays
SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)

Group: General Forum Members
Points: 617 Visits: 387
Yes, my apologies for not being more precise. When there are more than 8 CPU cores, I generally don't create a TempDB file for each one above the initial 8. It's pretty much as the post says. And as always: It depends...



Vegard Hagen
Norwegian DBA, blogger and generally a nice guy who believes the world is big enough for all of us.

@vegard_hagen on Twitter
Blog: Vegard's corner (No actual SQL stuff here - haven't found my niche yet. Maybe some day...)

"It is better to light a candle than to curse the darkness."
(Chinese proverb)


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search