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

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.

avatar image By mbourgon 16 asked Nov 28, 2017 at 10:04 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Nov 30, 2017 at 05:39 PM

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)?

10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Nov 28, 2017 at 10:04 PM

Seen: 41 times

Last Updated: Nov 30, 2017 at 05:39 PM