0

We had SQL Server 2008 SP2 and Microsoft told us to upgrade it to SP4 if we wanted any support from them. Now that Microsoft has warned us, my path to go to SQL Server 2016 SP1 is clear. Going back to my question, I upgraded our production servers on Saturday to SQL Server 2008 SP4. But stored procedures which were performing fast (this is a relative term) are running way too slow on Monday. I don't see any blocking. Users are not Memory pressure seems normal. I see Available as well as Free Memory when I open task manager. CPU is a bit high. But not pegged at 100%. My guess is, plan cache was flushed after rebooting servers post upgrade and queries are taking hit today. All index maintenance jobs were successful prior to upgrade. Should I have run full index maintenance after the upgrade? I ran DBCC CHECKDB after upgrade and it was clear. Replications are in sync. Modified Stats are updated daily. Let me know if you need any more information. Any insight will be a great help. Thank you.

  • Did you updated the stats with full scan after the upgrade for all the databases. So when it is slow did you checked the actual query plan and tried finding out what is possible cause – Shanky Apr 24 '17 at 17:35
  • Sounds like you've started to look at all the basics. That said, there isn't a lot of detail to go on. Any chance you have a performance baseline to compare the current "bad" status to a known "good" status? You might try to zero in on your slowest, most painful query and try to identify it's bottlenecks, and hope that it leads to am more general solution. – AMtwo Apr 24 '17 at 17:38
  • We have a table called ProcessingLog that logs time it took for each important part of the stored procedure to complete. The only variable in the equation is, SP4 upgrade and reboot. There may be parameter sniffing going on. @Shanky I use Ola's code suggested by Brent Ozar to update only modified stats. brentozar.com/archive/2016/04/… – Pat Apr 24 '17 at 17:49
0

We opened a support ticket with Microsoft and they gave us a tool to run to gather metrics when issue was in progress. I couldn't run the tool during business hours since I didn't want it cause problems for working processes. Then in the evening, update statistics job ran for almost twice long than before. Probably that produced better query plans for queries running on next morning i.e. on Tuesday. It was way better than Monday. I observed the performance today. It's back to normal. I didn't have to use Microsoft tool at all. Go figure! Thank you.

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.