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