I just completed a project deploying an application that is backed by SQL 2017. Unfortunately the dev environment was 2016, we couldn't get resources provided to us for a 2017 dev server, but we did test it pretty thoroughly before going live and generally everything performed better. We found one specific use case yesterday when a stored procedure with several optional parameters and one required parameter was executed with only the required parameter, that the sproc would run for about 90 seconds instead of 2-3 seconds.
I found that this was because the 2017 CE almost perfectly estimated the number of records it would return while the 2016 CE was off by more than 100 thousand records. Part of the query contains a section that looks for a partial match on an integer by converting to text and then creating a checksum value and returning records that have a certain number of the digits correct. The less correct cardinality estimate caused it to choose a radically different plan that ended up being much faster.
After finding this I was actually able to reproduce it in dev - someone had turned TF 4199 off, and once it was back on it produced the same behavior.
I "fixed" this by using a query hint with the legacy cardinality estimator, but to me it seems like a bug that this works and am a bit concerned about whether this will continue to work through patching. With TF4199 and the legacy CE enabled, SQL 2017 is getting a much better estimate (and completing in 300 ms instead of 3 seconds) than 2016 did with 4199 off, but it is still enough to trigger the alternate plan.
The primary problem is the type conversion, but the ID number must be an integer and don't see anyway around querying for a close string match to the original number for the way we generate the checksum, which effectively checks if any 8 of the 10 digits in the number are correct.
Does anyone think I need to worry about this fix breaking at some point in the future?