Contention on tempdb due to high concurrency temp table creation from multiple databases?

Question

I have an environment with ~100 databases, all with the same schema. There is a stored procedure on each database that creates a #temp table and drops it, and is running very often (every 30 seconds or so per user, and there are 1000+ users). It's doing a lot more than just this: 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.

Since all the databases create the same temp table, are they all competing with each other? Or does each database get its own version of the temp table in tempdb?

It seems like they are having contention because I see a large amount of PAGE_LATCH waits, all on the same page in tempdb (2:3:1041580), which is not a GAM, SGAM, or PFS page, and 90% of the waits seem to be from this same stored procedure across all databases. These waits are 90% of the overall waits on the server and are causing blocking.

I did a DBCC PAGE, and it appears to be sysobjvalues (from the object_ID 60 in the header). I ran:

SELECT OBJECT_Name(object_ID), * 
FROM sys.dm_db_database_page_allocations(2,60,NULL,NULL,'DETAILED')

I get 153 rows, not sure exactly what I'm looking at here. Looks like it's IN_ROW_DATA and LOB_DATA in allocation_unit_type_desc.

I have 8 tempdb files on a separate volume from the other files. I have 16 cores but my understanding is that since it's not an allocation page it might not help to increase the number of files. Using SQL Server 2017 Enterprise.

I feel like the best bet is to just tell the developers to remove the temp table here but that will require reworking the logic and will take some time. Is there anything else I can do to avoid these page latches while I wait for the dev fix?

Answer 1

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)

Answer 2

TempDB is a shared resource, so to a degree, yes, all sessions are competing for that resource. But, this has nothing to do with the table name being the same since SQL Server appends a unique set of characters to the end of each local temporary table name. It would have to do with creating / dropping temporary tables in general, and DML operations against those tables (that and if you are using snapshot isolation then it uses tempdb for the version store, and Triggers use tempdb for the inserted and deleted tables).

I do not believe that it is the temp table itself that is the issue since "once every 30 seconds" is not as frequent as you might think. I have worked on systems with thousands of transactions per second, and lots of temp DB usage.

I would look at:

1. What is being done with that temp table? How much data is being loaded into it? Are there lots of updates? If there are lots of rows, perhaps adding an index could help?

2. Is the temp table being created within a Transaction? Either in an explicit Transaction (i.e. BEGIN TRAN) or maybe in a Trigger? If yes, then I have seen that scenario cause blocking. It is better to create the temp table outside of the Transaction and once created, then do DML statements within the Transaction.

Also, since you are on SQL Server 2017 Enterprise, you perhaps could look into using In-Memory OLTP to swap out the disk-based temp table. I believe it would be creating an In-Memory User-Defined Table Type, then creating a table variable from that UDTT.