So were using SQL Server 2016 Enterprise and suddenly have an issue with a query on the Production server.
Works fine on Test and Dev and if we backup and restore the production DB to either of those works fine also.
SELECT * FROM ExchangeRatesSettlement WHERE RateTypeKey NOT IN (3,4,5,6)
now times out after an hour, It's worked fine for well over 6 months.
Takes about 10 secs on test from the backup.
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 from a NOT IN
subquery it goes back to 10 seconds.
I'm completely stumped.
We did have something similar on this machine a while back where a query with a CTE started timing out but if we pasted the CTE into the FROM in brackets it ran fine again.
The query above is actually a view on another exchange rates view that does a join and filter to settlement currency. Also if I run the same not in query directly on the view underneath it still works fine.
This is the plan from Production for the Query that stopped working and times out - https://www.brentozar.com/pastetheplan/?id=B1ARfgCWr
This is the plan from Test for the Query that stopped working on Prod (but still works fine on test, EDW builds with the same data every day) - https://www.brentozar.com/pastetheplan/?id=rJjwmxRbr
This is the plan on Prod for the one I updated to not time out - https://www.brentozar.com/pastetheplan/?id=BJlp7eRZr