So we used SQL Server 2016 Enterprise and suddenly have a problem with a request on the production server.
Works well on Test and Dev and if we also save and restore the production database on one of these jobs as well.
SELECT * FROM ExchangeRatesSettlement WHERE RateTypeKey NOT IN (3,4,5,6)
the time is up after an hour, it worked well for more than 6 months.
Take about 10 seconds on the backup test.
If I rewrite the query to be
SELECT * FROM ExchangeRatesSettlement WHERE RateTypeKey IN (SELECT RateTypeKey from RateTypes WHERE RateTypeKey NOT IN (3,4,5,6)
so it's an IN of a NOT IN
subquery it goes back to 10 seconds.
I am completely perplexed.
Some time ago, we had something similar on this machine, where a query with a CTE started to expire, but if we collided the CTE in the FROM in parentheses, everything would run smoothly.