- 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
- 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.
- 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.