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?
Best Answer
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: