Temp DB question

Question

Temp DB question

Alan Burstein

SSC Guru

Points: 61141
More actions
April 9, 2015 at 5:51 pm

#296699

I am considering a change to how we configure new SQL Servers... On our servers tempdb get's it's own drive. Data file and log file autogrowth is set to 10% / unlimited growth. Wouldn't it be better to just use up all the space on that drive since the drive is not used for anything else and autogrowth slows stuff down? It seams logical to me - does anyone see a downside to this approach?
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."
-- Itzik Ben-Gan 2001

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Alan.B (4/9/2015)
I am considering a change to how we configure new SQL Servers... On our servers tempdb get's it's own drive. Data file and log file autogrowth is set to 10% / unlimited growth. Wouldn't it be better to just use up all the space on that drive since the drive is not used for anything else and autogrowth slows stuff down? It seams logical to me - does anyone see a downside to this approach?

File growth should almost never be setup as a % so far as I'm concerned. And, there is only 1 downside to preallocating the entire disk and that is you will never know if a runaway query is in process. I've got a 16 core box with a 100 GB "T:" drive for TempDB. I've allocated 8 2GB files for data and 2GB for the log file. It's never had to grow. I have an alert setup on it for when it does grow because it will mean that someone might be doing something stupid that I need to look at. Having it grow and give me the alert on the first grow will give me time to figure out if it's stupid or not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Don't use up all the space on any of your drives. Always have a one or two mb file called "deletemeinemergencies.txt" that you can use to gain a little space in the event of one of Jeff's runaway queries.

But, if you want to fill the space you can, but just turn off auto-grow. You do lose that bit of monitoring like Jeff says.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

Thank you Grant and Jeff!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001