Temp DB recommendations for SCOM 201x

When someone asks you about TempDB sizing, does your jaw hit the floor?


Time to pull out my handy detective skills



Context & Best Practices 
SQL Server uses the tempdb database to store temporary objects. This can include temporary tables, stored procedures, work tables, and row versions. In short, most changes to user databases are routed through the tempdb database. Increasing the number of tempdb data files enables SQL Server to perform more concurrent operations, by distributing activity over multiple physical files.

Tip Modify properties of tempdb data files to have identical initial size and growth increment.


Support article



The Risk Assessment tool (RAS) states

Increase the number of tempdb files in line with best practice guidance. As a general guideline, in an environment where tempdb is used heavily, the number of tempdb data files should be between 0.25 to 1.0 times the number of physical processor cores. On servers with more than eight physical cores, start with eight data files, and then increase or decrease the number of data files as needed.

Example – 4 core SQL server needs at least 1 TempDB file


Supporting resources
2016 TempDB Database information https://msdn.microsoft.com/en-us/library/ms190768.aspx
TempDB sizing https://msdn.microsoft.com/en-us/library/ms345368.aspx
Optimizing TempDB Performance https://msdn.microsoft.com/en-us/library/ms175527.aspx

Leave a comment

Your email address will not be published. Required fields are marked *