I have a question regarding the splitting of TempDB in SQL Server, I know its a must in production environments as DBs Start growing and system becomes move complex.
In terms of a System Test / UAT environments, Should the exact same process be followed to replicate what's in PROD or is there no benefit in terms of having 1 TempDB file In these environments?
Assuming the answer would be what's in production should flow through all other environment, but is there anything debating against this?
Multiple files in tempdb are implemented to overcome allocation contention within the database, the first question is, are you facing allocation contention, have you monitored for this?
But, just like TF1118, it doesn't actually hurt anything to have multiple TempDB files (correctly configured, of course) even if you don't have such contention. Right?
Within reason. 4, fine. 8 probably fine. 64, not a good idea.
I typically recommend 4 files, no more without evidence of contention
Yep... absolutely agreed on the number of files as a part of "correctly configured". Thanks for the confirmation, Gail.