Need technical experts suggestion on an DB performance issue that I am facing after every application upgrade. Here is the current DB setup details for reference:
Database : SQL Server 2014
We have two node active-active cluster with 128 GB RAM and 20 Cores on one node.
There are 2 databases (1 production (200GB) and 1 reporting) running on one instance. Replication is configured between production and reporting database.
During front end application(.Net application) upgrade, as a defined step we stop the replication, so that no DB synching issue during the upgrade. Once the application redeployed with minor changes as enhancement, we again set back the replication in place and application work fine for couple of hours.
Here the issue rises after 8-10 hours of application upgrade and it starts creating issue on performance side like some of the screen/functionality running slow with a 50% degradation on performance.
During performance impact we have performed all the necessary health checks on the SQL Server and couldn’t find any blocking in SQL server. Server CPU and Memory utilization also looks normal.
This issue get auto fixed in 24 – 30 hours post application upgrade and things back to normal. This issue we are facing post every application upgrade.
Help: Can you guys suggest what could be the issue or any other checks that we should perform to find out the root cause.