Longer execution times at the end of the day

Shah 2015-11-04 15:09:34

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

Aaron Bertrand 2015-11-06 15:43:34
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?