4

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?

  • temp tables are not related to databases, but to sessions. And when you say "users," are these really different sessions, or are they just different users that may be connecting through an application that has a limited number of sessions? – Tony Hinkle Jun 5 '18 at 19:15
  • " I see a large amount of PAGE_LATCH waits" the number of waits is not important. Only the aggregate wait time (and sometimes signal wait time). – David Browne - Microsoft Jun 5 '18 at 19:49
  • Just to be clear, "dumping the whole thing out" does this mean the table is just being dropped, or are the results also being sent to a location outside of SQL i.e. BCP to file? – pacreely Jun 6 '18 at 9:34
5

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:

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.

  • Thanks, that helps to clear up if the name matters. As to how frequent, it's once per 30 seconds per user, and there are 1000+ users, and 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. I'll look into indexing the temp table, I didn't realize you could do that. It is not in an explicit transaction or a trigger, so I don't think that's the issue. I'll also look into In-Memory OLTP, have not explored that at all. Thanks! – Zaphodb2002 Jun 6 '18 at 3:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.