Longer execution times at the end of the day

We have a stored procedure which executes in less than 1 second during the day. The same procedure exceeds 30 seconds at the end of the day which is also the peak time for our system.

Below are some information about the stored procedure: Statistics for our data are calculated early in the morning. Auto update statistics is disabled for our database. In the stored procedure, we perform 30 left joins using the WITH (NOLOCK) hint. All tables are joined using the same primary key.

What might possibly make the procedure to take more time during peak processing periods ?

Thank you for your help

avatar image By Shah 1 asked Nov 04, 2015 at 03:09 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Nov 06, 2015 at 03:43 PM

Tough to say, could be simple resource exhaustion, could be blocking (yes, even WITH (NOLOCK)), could be that the plans you get in the morning don't work well after data has been skewed throughout the day. Have you considered updating the stats more frequently than once a day? Have you considered simplifying the query or the schema so that you aren't constantly running a query with 30 left joins?

10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x117
x24

asked: Nov 04, 2015 at 03:09 PM

Seen: 67 times

Last Updated: Nov 06, 2015 at 03:43 PM