Sudden slowdown of inline code on 2014, fast with old CE, but can't change.

mbourgon 2017-11-28 22:04:23

Here's a fun one hopefully y'all can help with.

A nondeveloper wrote this and embedded it in a system, years ago, as a DLL that calls inline SQL with sp_executesql. It was working fine until two weeks ago, and it now takes 50-ish seconds, where before it was under 10.

I show no schema changes to the tables.

Normal running of IndexOptimize to just update statistics doesn't correct it (I also show tables were recently REORGd, but we do that daily).

The IN clause is super fun, since the CTE returns a set of 50000 different values, ranging from the value "10000" to "30000", of which there are 6000 unique values.

If I grab the code and add the 9481 trace flag it comes back in under 10 seconds. I'm at a loss. The biggest table suck is getting about 3m reads, but I don't see a good way to add a filtered index on it.

My only other idea is a plan guide, but at this point I'm spitballing. I also see a whole bunch of stat "misses", with 20 rows expected vs 65k. All help, as always, gratefully appreciated. Pointing and laughing I'm used to.

Aaron Bertrand 2017-11-30 17:39:44
Do you have any examples of the query when it used to run fast? I wonder if you see the same plan shape, same stat discrepancies, and maybe lower SOS_SCHEDULER_YIELD waits. Do you have the ability to keep the database in old CE mode by lowering the compatibility level? Can you change the DLL to call a stored procedure (and hopefully change it to pass all those values in via TVP instead of a huge IN list)?