10

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.

  • Does the process essentially copy the data from the #Temporary tables to the permanent table, or is there additional transformation logic happening in that step? – Jon Seigel Apr 19 '13 at 16:42
  • At the step it is waiting, it is copying a single store's inventory records into the permanent table without transformation. We could operate inside the permanent table the whole time, but I think the programmer opted to use a #Temporary table as the holding area to prevent frequent updates to the data from converting into PAGE locks. – Paul Williams Apr 19 '13 at 17:07
3

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:

  1. Remove the IDENTITY property from the column in the final table.
  2. Generate identity values in each of the #Temporary tables.
  3. When loading the final table, combine a numeric identifier for the particular store with the identity values from step 2.

So if you have store ids 3 and 4, you would end up with final id values like this:

3000000001
3000000002
3000000003
...
4000000001
4000000002
...

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 ALLs them together, eliminating the need to copy the data at all.

  • Thank you for the response. I agree if that's the issue, using some manufactured key (or no key at all) might fix it. We've opened a case with Microsoft regarding this issue. I'll post the result here and accept your answer if they agree that is the problem. – Paul Williams Apr 23 '13 at 19:55
  • @Paul: Please let me know; I'm just as curious. Like you, I wasn't able to find anything on the web about this latch, but it's certainly reasonable that it's identity/sequence serialization. Whether or not that's the bottleneck is tough to say, though with 30+ threads competing for values, it seems likely. You could also try copying from each #Temporary table in series (instead of in parallel) to see if that helps. – Jon Seigel Apr 24 '13 at 2:17
  • 2
    The SQL Server engineer agreed it was probably the IDENTITY column. We took it off of the already wide clustered index and removed the column entirely. It was not necessary. Afterwards, these LATCH_EX waits went away. We could not duplicate the waits on SQL 2008 R2. The issue happened only on SQL Server 2012. – Paul Williams May 10 '13 at 13:17
  • @Paul: Thanks for following up. Very interesting. I'm guessing that the code that generates IDENTITY values was rewritten to use the new sequence generation stuff that was new in 2012. On < 2012, you may see a different latch type, though if there was no perf issue, then it seems there was a regression in the code. In any event, removing the IDENTITY column is the safest thing. – Jon Seigel May 10 '13 at 17:01
  • Instead of identity you could try to use a 'SEQUENCE' (which is new in SQL 2012) – Bogdan Maxim Jan 13 '14 at 6:52
6

(Updated February 2019)

This is an old post, that said I've finally managed to convince Microsoft that the very fact this happens is indeed a defect.

Update: MS Confirmed the defect and assigned it a bug # of 12628722.

I had seen this post this past November 2018 when we to began to suffer much the same after we'd upgraded from Sql Server 2005 to Sql Server 2017. A 3.3 million row table that used to take 10 seconds to bulk insert suddenly started taking 10 minutes on tables with Identity columns.

Turns out there are two issues behind this:

  1. Microsoft changed the behavior in Sql Server 2014 to force Bulk Inserts to run parallelized - in prior versions Bulk Inserts were given a Serialized plan.
  2. Once running in parallel on our 32 core box, the engine spent more time with the cores locking each other than actually doing the work.

Took me 4 weeks but just after the holidays I got a belated present from Santa - confirmation that the issue was indeed a defect.

There are a few possible workarounds that we found until this is fixed:

  1. Use Option (MaxDop 1) in the query to turn the bulk insert back into a serialized plan.
  2. Mask the Identity column by casting it (e.g. Select Cast(MyIdentityColumn As Integer) As MyIdentityColumn)
    • this prevents the identity property from being copied when using SELECT...INTO
  3. Remove the identity column as described above.
  4. Change the database compatibility mode to Sql Server 2012 or lower to re-establish a serialized plan.

Update: The fix MS will be implementing will be to return these sorts of Inserts back to using a Serialized plan. This is planned for Sql Server 2017 CU14 (no news on other versions of Sql Server - sorry!). When implemented, Trace Flag 9492 will need to be turned on, either at the server level, or via DBCC TraceOn.

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.