RE: DB server comes to a crawl every two weeks

SSC Guru

Points: 186127

January 2, 2014 at 5:05 am

Grab actual query plans for whatever query is responsible for "Existing orders with 200 lines take 10 minutes to retrieve."

One plan from when response is fast, one plan from when response is slow. Save the plans as .sqlplan files and post here.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden