TheSQLGuru (3/27/2014)
This is a concurrency issue, therefore I am 99.984% certain it is a CODE issue. Something somewhere is not properly handling locking and concurrency. As for why this is now starting on 2012 but did not on older SQL Server the most likely cause is that the optimizer is coming up with a different plan that is allowing it to happen and your code isn't dealing with it properly. the 0.016% chance could be a bug.And like someone else said, you didn't tell us much at all about details of code, structures, indexing, the other processes other than the sproc, etc., etc.
Did you update statistics with a full scan after upgrading to 2012? Also, did you update ANY piece of hardware or the VM during the upgrade?
?
Why is a concurrency issue? Locking mechanism did not change on 2012. Like I said, there is only one sproc that is always inserting there, so no way it can be a concurrency issue. And if that was the case, inserts lock the required pages or table, and block anything else ... insert ... and then sql increases the value. So next insert should use next ID, already increased.
I am almost sure this is a bug, but (or a VMware/SAN issue) but I can't reproduce.