We have a process that generates an inventory report. On the client side, the process splits of a configurable number of worker threads to build a chunk of data for the report that corresponds to one store out of many (potentially thousands, typically dozens). Each worker thread calls a web service that executes a stored procedure.
The database process for processing each chunk gathers a bunch of data into a #Temporary table. At the end of each processing chunk, the data is written to a permanent table in tempdb. Finally, at the end of the process, one thread on the client side requests all the data from the permanent tempdb table.
The more users that run this report, the slower it gets. I analyzed the activity in the database. At one point, I saw 35 separate requests all blocked at one point in the process. All these SPIDs had on the order of 50 ms waits of type LATCH_EX
on resource METADATA_SEQUENCE_GENERATOR (00000010E13CA1A8)
. One SPID has this resource, and all the others are blocking. I did not find anything about this wait resource on a web search.
The table in tempdb that we are using does have an IDENTITY(1,1)
column. Are these SPIDs waiting for the IDENTITY column? What methods could we use to reduce or eliminate the blocking?
The server is part of a cluster. The server is running 64-bit SQL Server 2012 Standard Edition SP1 on 64-bit Windows 2008 R2 Enterprise. The server has 64 GB RAM and 48 processors, but the database can only use 16 because it is the standard edition.
(Note that I'm not thrilled by the design of using a permanent table in tempdb to hold all this data. Changing that would be an interesting technical and political challenge, but I'm open to suggestions.)
UPDATE 4/23/2013
We've opened a support case with Microsoft. I'll keep this question updated as we learn more.
UPDATE 5/10/2013
The SQL Server support engineer agreed that the waits were caused by the IDENTITY column. Removing the IDENTITY eliminated the waits. We could not duplicate the issue on SQL 2008 R2; it occurred only on SQL 2012.
Best Answer
Assuming you can isolate the problem to the generation of identity values (try removing that column as a test), what I would recommend is this:
IDENTITY
property from the column in the final table.So if you have store ids 3 and 4, you would end up with final id values like this:
Or something similar to that. You get the idea.
This will eliminate the need to serialize on
IDENTITY
generation while preserving uniqueness in the final result.Alternatively, depending on how the process works, insert the final calculated id values into the #Temporary tables. Then you could create a view that
UNION ALL
s them together, eliminating the need to copy the data at all.