What is hot spotting in the context of adding files to tempdb?

Question

I'm trying to find out whether it is possible to add tempdb files to a SQL Server without having to restart the SQL Server service. I saw this answer here on Database Administrators:

• Tempdb Add files require Restart

And one answer states:

ADD - no outage required. Although as Sean from Microsoft pointed out, SQL will prefer to use the lower filled files. If you are going from 1 data file and adding more, then SQL will use the new ones for a while, but your performance won't be worse than only having one file. However, if you have 2+ already and add one more, then it will hotspot on the new one and decrease performance.

However, a comment cautions the following:

I would put an addendum on the "Add" part: "Add: No, but you'll most likely be imbalanced so you'll be hot spotting which could make things much worse."

I have the following questions about that comment but was instructed to ask those questions in a new question of my own (this one) rather than asking the commenter via comment in that question's answers.

Specifically:

1. What is hot spotting? (I got some info via Google but not in detail what happens with hotspotting on tempdb after adding files)
2. What about hot spotting makes things much worse in tempdb?
3. What specific things in the DB would get much worse?

Answer 1

1. What is hot spotting?

   "Hot spotting" in this context means that, even though tempdb has multiple files, all the I/O work is being done in a single file. If tempdb is busy enough to justify adding files, the imbalance that leads to hot-spotting (due to proportional fill) will be short-lived, so I think the warnings may be a little Chicken Little. In my experience, anyway.

2. What about hot spotting makes things much worse in tempdb?

   I think it is deemed to be worse in tempdb because it takes a brunt of the write activity in most workloads. You can certainly suffer similar problems in user databases, but since you're already trying to solve a problem in tempdb...

3. What specific things in the DB would get much worse?

   Write times, mostly. Imagine everyone trying to use the same ATM, even when there are 7 other ATMs nearby. Only so much can be written at any point in time; everything else has to wait. With more files (and enough cores to schedule the work), the I/O can be spread more evenly.

   Just make sure:

   • you have an even number of files
   • all the files are the same size
   • all the files have the same autogrow settings
   • the number of files is somehow driven by number of cores
   • depending on version (these are enabled by default in more modern versions of SQL Server), you are using trace flags 1117 (grow all data files at once) and 1118 (don't use mixed allocation extents).

Answer 2

1. What is hot spotting?

Aaron is correct and I'm not going to rehash what he has said above, however it's not just about disk IO. The main part that most people have issues with in TempDB is due to contention on certain tracking structures.

Since having multiple tempdb files allows the proportional fill and round robin algorithms to effectively take place in being "fair" across allocations, adding a new file with no allocations throws that off a bit. I do disagree that it's a "chicken little" warning (see product updates below) if you start to see PAGELATCH_* waits on said new file and not many or any on other files. This generally happens on systems that have high TempDB activity and already have more than a single file.

Please note that there are options in SQL Server 2019 to change some of the underlying system tables to in-memory tables which can have an improvement as in-memory objects are allocated differently than disk-baked tables. Disk-based tables are the traditional tables that we all have been working with over the years. SQL Server 2014 introduced memory-optimized tables. SQL Server 2019 can handle some allocation metadata in memory-optimized tables.

Another change was made in SQL Server 2019 to help with concurrent PFS changes, which is generally what the contention for in-memory structure in allocation are having the PAGELATCH_* waits.

2. What about hot spotting makes things much worse in tempdb?

Nothing IMHO. Yes, TempDB has more items that can cause writes to it without being used directly so it can hinder some items. However, a very busy user database in terms of rate of data change is just as bad. It's not limited to just TempDB.

3. What specific things in the DB would get much worse?

I really like Aaron's analogy! That's the essence of what's going on. What really does get worse is the allocation and tracking of space for objects in the database. If your user database is mostly static (low rate of change) or your TempDB isn't really being used, you won't notice anything. If, however, it's a fairly busy server you may start or exacerbate pagelatch waits which could lead to blocking convoys.

Aaron already pointed out that on older version there are trace flags to make sure that uniform extents are used and that all files in a filegroup grow together (Aaron points out 1117 and 1118 which are NOPs in 2016+). The other thing I'd like to point out again is that this isn't just for TempDB but for any database, and the physical layout should be thought through depending on needs.

This is not just for hotspotting issues but is applicable to other parts of the system such as backup/restore, file management, filesystem metadata fragmentation, etc., which can all be helped by having multiple files.

You can see allocation structure contention by looking for a waitresource on a PFS page (which is page 1, and then every 8088 pages). If you see that all in the same file (2:file:page) then you know this is occurring.