7

I was given a SQL Server 2008R2 to run, but my DBA skills are rusty after some years away from the day-to-day ops, so please bear with my (somewhat) silly questions.

This SQL Server is running in Azure with 14GB RAM, 8 cores and 3 sets of striped disks (two stripes per set).

Whenever I have to apply an Update and reboot the machine, Users experience a MAJOR system slowdown during the first 20/30 minutes of the next business day.

In ALL occasions I was online inspecting the behaviour, the only significant change I could detect was a sudden increase in Page Writes/s. Further investigation pointed to a sudden increase in TEMPDB i/o ops. I have then tried to increase TEMPDB's base size, but this still happens. Event Viewer logs report nothing. It's like SQL Server needs to "re-tune" itself under load after reboot.

As I said, the slowdown lasts for 30 minutes tops, but it is obviously annoying and I can't figure out a way to prevent it.

Any ideas?

Thanks in advance,

  • 3
    Sounds like the Plan cache is warming up. Have you measured memory consumption during the 30 minutes? – James Anderson Mar 16 '15 at 13:03
13

When you restart the service or reboot, the buffer pool and plan cache get wiped out as a result, and so when the server starts back up again, they don't get loaded until users start executing queries.

What you can do to mitigate this is have a stored procedure aimed at simply running some queries that will load up your most frequently-used data into memory. Call that procedure after a restart, and you will feel that initial delay instead of your users.

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.