tempdb

Question

tempdb

Tara-1044200

SSCoach

Points: 15951
More actions
November 22, 2010 at 7:58 am

#243192

I have 2 TB of space which i can use only for tempdb and would like to configure 8 files, let me know if this is best. planing to create 8 files each with 100GB(initial size) with 10% of growth.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Tara-1044200 SSCoach Points: 15951 More actions · Answer 1

doubt.. if i configure 8 files each with 10GB and once the 1st file get filled does to jump to the second file or does it grow 10% on the same file, which one would occur 1st. Thanks

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

Why 8 files?

What's the max size your TempDB has ever reached?

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

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 3

Why would you want to create 8 files? Do you have contention on the tempdb allocation map pages? If not, there is no need to create piles of tempdb data files. Take a look at this thread for more info.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

I am trying to make 8 files just so i have better performance. Yes there are lot of activities on tempdb, my users insert millions of records into temp tables and query from there.

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

Unless you have a specific form of contention, spitting into 8 files on the same drive may not give you much if any performance gains. Are you seeing contention on the allocation pages?

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

Jeffrey Williams SSC Guru Points: 90314 More actions · Answer 6

Whether or not you need 8 files - you should not set the auto-growth to a percentage. And, just because you have 2 TB of space available - does not mean you need to use it.

I definitely would not create 8 100GB files for tempdb - that just seems to be way over the top. You would have to be working with a system that is at least 10TB, very large tables and huge cross join type queries to use up 800GB of temp data.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 7

I guess my question would be, what are you doing where you'd ever need almost a T-Byte of TempDB? What's the total MDF/LDF size for that server?

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

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

Very good question. I had a system with a 1TB database that heavily used tempDB (lots of temp tables all the time). Our TempDB? 6 files of 10GB each. More than enough (yes, we did have quite severe allocation contention hence the splitting of the files)

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

franco SSCommitted Points: 1642 More actions · Answer 9

I would also have a look at Microsoft Best Practices for storage:

http://technet.microsoft.com/en-us/library/cc966534.aspx

Franco