1

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?

0

2 Answers 2

5

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.

3
  • Aaron,I split my TempDB in AOAG environment into 8 files with equal size. TempDB disk size is 200 GB (out of that I assigned 15-20% for templog file with NO file growth enabled and remaining space / 8 = 8 TempDB data files with NO filegrowth enabled ) and enabled flags 1117 and 1118. Now the disk is showing in Red but the TempDB files has 99% available space.So can you please tell me anything to worry in this case ? Thanks Aaron Commented May 31, 2016 at 14:17
  • 1
    No file growth at all? Well, that is a bit risky, allowing room for one file growth (and setting up alerts to detect it) might be the difference between an immediate outage and enough time to react to prevent one. Commented May 31, 2016 at 14:36
  • Got it Aaron,will plan it that way.I will leave 5% free space of total disk and enable the last data file for autogrowth by 100 MB.Thanks Aaron. Commented May 31, 2016 at 14:54
5

You pretty much always want more than a single TempDB file. I'd certainly not go above 8 files in most situations; and probably more like 4. Having said that, the only way to know the best configuration is to test it.

This applies equally to SQL Server instances of all kinds, including high availability setups, not just AlwaysOn Availability Groups.

1
  • Thank you Max for clarification and my situation is we have FileStream enabled and earlier we faced multiple issues,so want to take advice.Thanks for your time.This is what I am looking for ---- "This applies equally to SQL Server instances of all kinds, including high availability setups, not just AlwaysOn Availability Groups." Commented May 27, 2016 at 16:04

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.