I understand there are plenty of factors to consider when sizing Tempdb. But is there a recommendation from Microsoft for configuring initial size of Tempdb. I mean, like 25% of data drive or something like that ? In one of our servers Data drive is 500 GB (data size is 450 GB), Log drive is 150 GB but Tempdb is sized 25 GB. I wanted some microsoft's recommendation for deciding on the initial size of Tempdb.
Answer by Kev Riley ·
Given that tempdb usage is driven by workload, there is no such recommendation that anyone can give. You could have a multi-TB database but if all the queries were lightweight then you'd see little use of tempdb; conversely a 50MB database, with heavy, concurrent queries doing hash aggregations and using snapshot isolation could easily use GBs of tempdb.
Your database already exists (I presume by the way you've phrased the question) so what empirical data do you have from running it? This will be the best indication for your environment.
One of the best practices is to create tempdb on it's own volume - so if this is a physical environment or limited in such a way (for example, ethereal storage on cloud instances) then use the full capacity as you will lose nothing by having an oversized tempdb.