Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Question regarding TempDB Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2012 12:28 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
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?
Post #1373991
Posted Wednesday, October 17, 2012 1:10 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
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 2008, MVP
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

Post #1374014
Posted Wednesday, October 17, 2012 1:50 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1374032
Posted Wednesday, October 17, 2012 2:42 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
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.
Post #1374058
Posted Wednesday, October 17, 2012 7:31 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 11, 2014 6:50 AM
Points: 517, Visits: 346
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)

Post #1374105
Posted Thursday, October 18, 2012 1:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
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 2008, MVP
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

Post #1374181
Posted Thursday, October 18, 2012 4:50 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 11, 2014 6:50 AM
Points: 517, Visits: 346
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)

Post #1374265
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse