I have an Availability Group set up on SQL Server 2014 running on a two-node Windows Server Failover Cluster. The setup consists of two stand-alone instances + synchronous with automatic failover.
Many Microsoft articles I've read advocate using multiple files for TempDB to increase performance. It seems they recommend using 8 files.
Should I do that in the case of a this configuration? Will it improve performance?
Best Answer
Where do you see an explicit recommendation of 8 files? This is certainly not universal. The guidance is 1 file per core, up to 8 cores. If you have more than 8 cores, start with 8, and only add more (in groups of 4) if you actually observe the type of allocation contention that can be relieved by additional files (see this post for a way to determine this). If you're not seeing any of this contention, there's nothing more to do.
Be sure all files are equally sized, with identical autogrowth settings (specified in MB, NOT %), and that you have TFs 1117 and 1118 enabled (though those are no longer necessary starting with SQL Server 2016). This post may also be useful, as well as the links it points to.
Do not make your file sizes small to start out with; this is a common tactic that almost never works out well. Just pre-allocate all of the space you can afford to give them. Forcing them to grow (and then shrink on every restart or failover) just adds unnecessary pressure on your workload, and doesn't accomplish anything (what are you going to do with temporarily free space?). If tempdb is going to fill the drive(s), it doesn't really matter if the files started out small or not; it's still a problem when you get there.