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

Improve this question
2
  • 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
    Commented Aug 2, 2017 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
    – Hannah Vernon ♦
    Commented Aug 2, 2017 at 21:23

1 Answer 1

Reset to default
-1

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

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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