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.
Sql-server – Queries are performing bad after SQL Server 2008 SP4 upgrade
performanceperformance-tuningsql-server-2008
Related Question
- Sql-server – SQL Server 2008 datetime index performance bug
- Sql-server – RESOURCE_SEMAPHORE_QUERY_COMPILER waits in Microsoft SQL Server 2008 Enterprise
- Sql-server – How to a connection for one DB block a connection to another DB in SQL Server
- Sql-server – How to resolve RESOURCE_SEMAPHORE and RESOURCE_SEMAPHORE_QUERY_COMPILE wait types
- Sql-server – Slow SQL Server 2008 R2 backups after Windows update
- Sql-server – SQL Server 2008 R2 Performance Degradation – High Lazy writes/sec and free list stalls/sec
Best Answer
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.