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.
By mbourgon 16 asked Nov 28 at 10:04 PM