Contention on tempdb due to high concurrency temp table creation from multiple databases?
We are loading potentially thousands of rows into this temp table and just dumping the whole thing out, basically it aggregates a bunch of data.


Temporary tables can be a big performance and reliability win, when used correctly.

For example, it can be very useful to store a relatively small (and narrow) intermediate result set in a temporary table so SQL Server can compute automatic statistics on the data, or so the user can add specific indexing.

Temporary tables are most useful when they allow the optimizer to produce a better sequence of plans overall than a single monolithic query.

It is also possible to use temporary tables poorly. Simply storing rows in a temporary table before "dumping the whole thing out" sounds counter-productive. Always bear in mind that temporary tables are not free, and can contribute to contention.

Aside from using temporary tables in a sensible way, try to arrange things so you take advantage of temporary table caching. See my article SQL Server Temporary Object Caching for details.

Ensure you are running the latest cumulative update, since there have been several performance optimizations and bug fixes employed, for example:


PFS page round robin algorithm improvement in SQL Server 2016 and 2017 (2017 RTM CU7)
FIX: Heavy tempdb contention occurs in SQL Server 2016 or 2017 (2017 RTM CU5)