2

I'm at a new place and working on several issues. One item that was passed to me was this proc that used to take a few seconds is now taking well over a minute or timing out.

The data hasn't changed too drastically.

The filesReceived table has 902,596 records.
The fileuploads table has 629,862 records.

We run SQL2016.
Can you see if there is anything I can do to help speed this back up?

The plan: https://www.brentozar.com/pastetheplan/?id=BJc4DsywZ

  • 1
    Actually posting this question helped me to remember something. When they took the database compatibility to 2016 it was without updating statistics. I'm sure the CE is off on the plans. I set the database back to 2008 and it seems to be back to normal. If you do happen to look at the query plan and recommend anything please do so. – Shellz Aug 2 '17 at 19:59
  • You might try adding OPTION (RECOMPILE) to the end of the 2nd INSERT statement. The WHERE clause has a ton of optional parameters that may be causing issues for the query optimizer. @PaulWhite has an article you may be interested in that talks about parameterization, and recompile options etc – Max Vernon Aug 2 '17 at 21:23
-1

Easy answer for this. Updating statistics takes care of this slowness.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.