3

When I come to work on monday some stored procedures take several minutes to execute and STATISTICS IO shows a lot of PHYSICAL READS and READ AHEADS going on. However, after running the procedures 10-20 times with different parameters, the execution time drops to less than one second.

I think that for some reason the data buffer expires over the weekend, and it takes a lot of time to reload the buffer on monday.

How do I make sure my application works fast even if it's not used regulary. Is there a way to persist the buffer, or should I rewrite the procedures in a way that doesn't rely on the buffer?

  • I would updates stats for table involved in storead proc and run sp_recompile and see the performance. I sense outdated stats reason for slowness – Shanky Jul 16 '14 at 12:32
  • 2
    The data buffer may be flushed by activities such as reorganizing indexes, etc, which are aften scheduled on weekends. But several minutes versus 1 second is a big difference. This suggests that there is another underlying problem such as suboptimal indexes. – RLF Jul 16 '14 at 12:59
  • What does execution plan says, IMO this question is openended and only speculations can be made but if you can post execution plan with DDL and DML more acccurate answers can be provided – Shanky Jul 16 '14 at 13:16
  • That sounds like how I feel on a Monday before I've had a chance to get some coffe! ;) ...seriously though, it sounds like some data might be cached but gets dropped from the cache over the weekend, maybe to make room for something else? – FrustratedWithFormsDesigner Jul 16 '14 at 13:58
  • Try to use your proc with OPTION(RECOMPILE), may be it was parameter sniffing – user2766490 Nov 18 '14 at 11:28
0

The perf problem likely comes from IO that is cached under the week. The weekend work evicts those pages.

RAM is ~100x faster than sequential disk IO which again is ~100x faster than random IO. That explains why performance is falling off a cliff.

Rewrite your queries so that the working set of touched pages is smaller. This might involve adding indexes. Or, change the work that is being done over the weekend to touch less data. Or, add RAM. Or, add a cache warming job on 04:00 AM Monday that pulls the data into cache that you rely on by executing representative dummy queries.

0

Most likely activity on the server over the weekend has caused your data to be evicted from memory. This is just the way life is in a shared environment with limited resources. It would be good to understand what's happening over the weekend and how your usage affect other users of this service.

You could schedule a job for early on Monday, before anyone is in the office. It would run your SPs with the various parameter values to warm up the bufferpool with the data you will be using. This will evict whatever is in memory currently, slowing the process that is using it.

0

Yes, it's all about warming, even using SSD storage will give you only a certain amount of benefit, overall double based on my testing with small and large datasets read from "disk".

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.