question

Pradeep407 avatar image
Pradeep407 asked

Is there a recommendation from Microsoft for configuring initial size of Tempdb. I mean, like 25% of data drive or something like that ?

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.

tempdb
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

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.

3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the response Kev.

Well, we are onboarding a few servers into our support and i am not very sure at the moment how much of data processing goes on in the server in question. When we run index maintenance job, tempdb gets bloated because the SORT_IN_TEMPDB is set to ON. I agree there is no hard and fast rule for this, but i was looking for some insights in terms of ratio of data drive to tempdb drive, or atleast a good place to start. (Yes, tempdb is on a different own Volume)

Thanks..

0 Likes 0 ·

In that case give tempdb the entire volume, and then resize down as necessary. Or not.

0 Likes 0 ·
KenJ avatar image KenJ Kev Riley ♦♦ ·

Agreed. I give tempdb most of the volume then set up a sql agent alert to notify me if it auto-grows so I can troubleshoot and/or (more typically) grow the volume

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.