0

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.

Basically,

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.

---Update

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

  • Is RateTypeKey column sort of string type? – Denis Rubashkin Jul 17 at 15:06
  • RateTypeKey is an int. I've updated the question with the plans – Steve Hall Jul 18 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.