0

I've read it on a number of different sources, and saw it presented by multiple people at different events: "Temp DB should be pre-sized to 20% of your biggest content"

As I'm doing a 2010 farm health check I see the TempDB was pre-sized at 1Gb almost 2 years ago, has been set to autogrow unlimited, and it's still 1 Gb.

Would there be any benefit in increasing it to 25Gb or 50Gb? What and why?

Improve this question

1 Answer 1

Reset to default
3

  • TEMPDB is a temporary database that contains all Temporary user objects such as: global or local temporary tables, table variables, cursors. It also includes internal objects created by the SQL Server Database Engine; For example, work tables to store intermediate results for spools or sorting. All the mechanism of row versions is being handled and managed by TEMPDB.Consider the tempdb a working area for SharePoint operations. Dumping 300 list items into a SharePoint list?? Guess where the operation hits first, yep, tempdb.

  • TEMPDB is one of the important systems DB regarding the health of the SharePoint. In SharePoint almost every action/request is generating work in the TEMPDB.

  • So its strongly recommend, Create the TEMPDB database on the fastest storage available
  • Pre-allocate space for your TEMPDB files by setting the Initial File size to a larger value so it can accommodate the typical workload in your environment, you can go by a rule of thumb that the size should be 25% of the largest content DB or 10 % of total content database size, which ever is greater.

  • Also growth should be automatically and autogrowth fix size in MB.

  • this database may grow in time to a size larger than the desired size. A simple restart of SQL Server resets the size of the tempdb to its last configured size. The configured size is the last explicit size that was set by using a file size changing operation such as ALTER DATABASE that uses the MODIFY FILE option or the DBCC SHRINKFILE statement.

Now just think, if you have large operation going on the Sharepoint and temp DB is small in size then how much call tempDB make to get the extra space. that's why its recommended that set a presize and set auto growth to fix size.

Please see the below links to understand more: http://www.webperformance.com/library/casestudies/sharepoint/page4.html

http://blogs.technet.com/b/sqlpfeil/archive/2012/07/14/four-tips-for-sql-tuning-for-sharepoint-part-3-tempdb.aspx

Improve this answer
2
  • Thank you for your reply Waqas. Very helpful explanation, and that case study you linked to is indeed quite interesting. Going back to my question, if my TempDB is set to auto-grow unlimited and has not gone past the 1GB, does it mean the load on this farm does not go past the 1GB of temp? If it has not grown beyond 1GB, could it somehow be limiting performance and I should therefore still manually increase its size?
    – Jorge Carvalho
    Commented Mar 21, 2014 at 3:00
  • yes its depend upon the load on sharepoint. there is another factor which may reduce the size if you reset the sql server service or server then temp db size will reduced...i would go with recomended path which is 25% of the largest content db or 10% of the total databases. just set this intial with with unlimited growth then no need to worry.
    – Waqas Sarwar MVP ♦
    Commented Mar 21, 2014 at 3:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.