What should be the standard best practices drive size for tempdb size in capacity planning.

Question

What should be the standard best practices drive size for tempdb size in capacity planning.

forsqlserver

SSCoach

Points: 18902
More actions
November 16, 2011 at 2:11 am

#251909

What should be the standard best practices drive size for tempdb size in capacity planning.
Thanks

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

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

This is one of them where "how long is a piece of string" comes in to play.

Unless you know excatly what is thrown through TempDB its a tough one to guess at, you might have small TempDB usage or large, rebuilding indexes in TempDB, row version stores for database isolation levels etc

So I dont think you couldn't put a best practise on general TempDB sizing.

One thing I would say with my experience with Dynamics AX and TempDB is that AX wants the db to be in snapshot isolation or read committed snapshot, cant remember which, that then switches on the row version store, AX was never friendly cleaning up SPID's so always kept the row version store quite full, so we ended up with a TempDB which is the size of the actual DB * 1.5 so that we wouldnt have any issues with index rebuilds or row version store issue over night.

Edit, put could instead of couldn't

forsqlserver SSCoach Points: 18902 More actions · Answer 2

We have applications are:

SFS,SUNGL,Payroll,MOSS,Biztalk,NewGen,FIM

for these applications have any idea...

Thanks

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

As Anthony said...

anthony.green (11/16/2011)
This is one of them where "how long is a piece of string" comes in to play.
Unless you know excatly what is thrown through TempDB its a tough one to guess at, you might have small TempDB usage or large, rebuilding indexes in TempDB, row version stores for database isolation levels etc
So I dont think you couldn't put a best practise on general TempDB sizing.

The names of the apps don't help. You're going to need to do some load testing and see what happens.

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

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 4

forsqlserver (11/16/2011)
What should be the standard best practices drive size for tempdb size in capacity planning.

Optimizing tempdb Performance

http://msdn.microsoft.com/en-us/library/ms175527.aspx

Capacity Planning for tempdb

It's a big document but you may look for very specific what you need e.g. DBCC CHECKDB or User-defined functions.

http://msdn.microsoft.com/en-us/library/ms345368(v=SQL.105).aspx

David Wiseman SSCommitted Points: 1713 More actions · Answer 5

Size will depend very much on the individual environment, but you should try to avoid the need for auto-grow.

Tempdb can be critical to your database server performance and you should also consider how many tempdb files you create and where to place them. Creating multiple data files spread across different drives can improve I/O performance. The files should be created the same size to spread the load evenly among the drives.

Another reason for creating multiple files is to reduce allocation contention (e.g. PFS, SGAM etc). On busy systems with heavy tempdb usage, you might need as many as 1 file for every CPU core on your server. If you have contention in your tempdb, adding more files (even if they are on the same volume) will help. Note: If you don't have an allocation bottleneck it wouldn't make sense to create more than 1 file on each volume.

DBA Dash - Free, open source monitoring for SQL Server