We had a similar problem at our company where the entire SQL block was generated with a massive IN-list before being executed. Execution of the code would take forever and the execution time would steadily increase over the year as more values for the IN-list where added to the database. So in our case it was not the result of a SQL upgrade but I think the same solution might apply.

We used the same solution as Eirikur Eiriksson suggested. In our situation it was quite easy to change the code to put the IN-list into a table variable and then add an INNER JOIN to that table variable in the code instead of using the original IN-list.

I don't know how easily you can change the code, but if you are able to make changes you could try to insert the IN-list into a temp table or table variable and then use that.

So instead of using this:

SELECT ColumnA, ColumnB, ColumnC... FROM TableA WHERE ColumnA IN (Val1, Val2, Val3,…)

you could try something like this:

DECLARE @TTable (Tid int)

INSERT INTO @TTable (Tid)

VALUES (Val1), (Val2), (Val3),…

SELECT ColumnA, ColumnB, ColumnC... FROM TableA INNER JOIN @TTable ON TableA.ColumnA = @Ttable.Tid

The example is simplified code and, as always, should be tested before using it on a live-database. And it all depends on how easily you can change the code. We were able to change it fast without too much trouble, test it and implement it quickly. But you might not be that lucky.