Temp DB recommendations for SCOM 201x

  • Article

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

surprisesign

Time to pull out my handy detective skills

detectivehat

 

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 sqlconcurrencytempdb

 

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