For one of my servers( SQL server 2008R2) we can continuously witness High Page Reads/sec during business hours say: from 7 AM to 9 PM EST.
I kept the counter settings to monitor the same and found Page reads /sec to be constantly high with avg value of 4225 and max value of 60489. In addition during this High Pages read values PLE also remains very low, avg 90 and max of 270.
Therefore I went and checked for the process running during that duration :
Log backups occurring hourly
SQL Server agent job kicks a SP which checks the conditions and do Updates and Inserts accordingly.
There are other jobs which executes few other SP's as well, but they are scheduled run once a week on same date:
I believe the 2nd point to be a culprit, but is there a way I can be sure if that is the case and if yes, what should be the first thing to look with?
Ram: 16 GB, CPU 16 core
Kindly suggest! Thanks..