January 5, 2011 at 9:55 am
Hi folks,
i am looking for the size of temp db an instance of sql server.Where the instance contains 1 TB of database in the prod environment
January 5, 2011 at 10:07 am
Honestly, the best way to find out how much tempdb you need is, set it to a best guess, and let it auto-grow.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 5, 2011 at 10:39 am
It depends on the kind of workload, on how much usage of temp tables and table variables there is, on how often queries spool, sort and hash, on trigger usage, usage of the snapshot isolation levels and other things. There's no formula based on database size to calculate tempDB usage.
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
January 5, 2011 at 10:59 am
AK1516 (1/5/2011)
Hi folks,i am looking for the size of temp db an instance of sql server.Where the instance contains 1 TB of database in the prod environment
Do you want to know the current size of tempdb, or do you want to do capacity planning for tempdb?
If the first, run sp_spaceused.
If second, follow the advice of GilaMonster and GSquared.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 11, 2011 at 2:26 pm
We follow the old school "best practice" of setting the number of files to the number of cores and the size of tempdb to 2x the amount of physical RAM. So, if you have an 8 core system with 32GB of RAM, tempdb would have 8 files set to 8GB each.
However, everyone else that posted is 100% correct is that it really depends on your workload. Many times when we setup a server we rarely know how it's going to be used. So, we use what i mentioned above as a starting point, and 95% of the time it works out just fine.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply