LATCH_EX Waits on Resource METADATA_SEQUENCE_GENERATOR

(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.

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.