Size of temp db

Question

Post reply

Size of temp db

mah_j

SSCrazy

Points: 2931
More actions
February 26, 2013 at 6:39 am

#271346

Hi
Is there any effect on performance if I empty the temp db by restarting SQL server,and increase the size of it and also outo growth,or will be worse?
the mdf of temp db is 271 G and ldf is 9 G for a database with 563 G mdf

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

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

WWDMark Hall of Fame Points: 3157 More actions · Answer 1

Is your TempDb on it's own disc or is it on the same disc as the other system Db's?

MCITP
MCTS - E-Business Card
Twitter: WWDMark

Try not! Do or do not, there is no try

email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk

mah_j SSCrazy Points: 2931 More actions · Answer 2

It is on the same disk that mdf and ldf file are located but not C (os drive)

WWDMark Hall of Fame Points: 3157 More actions · Answer 3

Check this link out, it may help:- http://msdn.microsoft.com/en-gb/library/ms345368(v=sql.105).aspx

If you've got the disc space and you're having performance issues with the Tempdb, I'd definitely look to move it to it's own disc which could boost I/O and reduce contention etc. The link above should also give you some ideas.

hope it helps.

MCITP
MCTS - E-Business Card
Twitter: WWDMark

Try not! Do or do not, there is no try

email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

mah_j (2/26/2013)
Hi
Is there any effect on performance if I empty the temp db by restarting SQL server,and increase the size of it and also outo growth,or will be worse?
the mdf of temp db is 271 G and ldf is 9 G for a database with 563 G mdf

I'd have to say that 271G is a bit large. I'd start looking for any code that uses more than a billion reads that then wittle down from there. A TempDB as large as you have typically means that there's some nasty many-to-many joins that someone overcomes with DISTINCT or GROUP BY. Of course, if you have an index almost that large, a rebuild of such an index may also be the "problem".

--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)