AXISHK
asked on
Size of TempDB
What should I set for the size of TemDB database and log file ? Will it be too small for default size of 8MB ? How to determine the size on SQL 2008 R2 server ?
Tks
Tks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Temporary user objects like #temp table and @table variables
Internal objects like work tables,intermediate results, MARS, work file, sorting etc
Version store, snapshot isolation level and change tracking
Service Broker, database mail.
DBCC CHECKDB, Online Indexing etc.
8 MB is too small, however there is no good number for this but there should be enough size to avoid constant tempdb expansion, the initial tempdb size depends on your workload and whether you are using any of the above, Start with an average size then monitor the file size growth for a week or two then get then do some math to get the good number and resize the tempd so that tempdb growth is not triggered frequently.
There is no issues of being tempdb too large until you are running out of free space either in tempdb or the drive, but make sure that instant file initialization is on for the sql server service account, this helps to reduce the file expansion time in the event of auto-growth. Another import thing is the number of files of tempb general recommendation is to have .5 to 1 files per cpu cores to reduce tempdb contention and disk band width, make the size of the files equal to get the benefit of proportional fill.