Tuesday, July 8, 2014

Ascending Key in SQL 2014

Don’t know if you are all aware of that, but just to make sure…

SQL 2014 introduced a new cardinality estimator that gets automatically activated once you switch the compatibility level to 120. In general this is a very cool thing, and in the very most cases it brings better results than the old one. There is one scenario though where you might face problems with it: The cardinality estimator was never very good with ascending keys. (e.g. Identity columns.) The old one was bad, the new one is exactly as bad… The problem is… For the old one there was a workaround with Traceflags 2389 and 2390. Those DO NOT work with the new cardinality estimator anymore, they are just ignored. So if you are using those TFs be aware of that change.