I am a sysadmin and I am not very well versed in SQL Server metrics. I have been having issues with one of our DB's and the DBA doesn't seem too knowledgeable about measuring performance, so I'm trying to determine why we are having these issues.
This is a DB with 134 GB data file and 25 GB log file currently. The DB is encrypted at rest (TDE) and uses a key stored in Azure KeyVault (same region as the server). It is running in replication mode and replicating to a single SQL Server instance. The main instance runs on an Azure VM (8 vCPU, 56 GB RAM) and replicates to a backup instance in Azure SQL.
The main issue we are having is Page Writes/sec. It is currently always around 250 writes/sec or above, and rarely drops below 200. I have read that 90 writes/sec is a normal threshold. Sometimes, stored procedure executions can increase from a few milliseconds to half a second or more, severely affecting our app performance.
Meanwhile, the PLE is 60,766 secs, the Buffer Cache Hit Ratio is 100%, and Page Reads/sec are at 0.2/sec. So this is most likely not a memory issue, right?
So now what is the best way to determine what is causing the largest number of page writes on an ongoing basis? How can we know if it's simply poor design, code needing optimization, or some other issue?