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 :

  1. Log backups occurring hourly

  2. SQL Server agent job kicks a SP which checks the conditions and do Updates and Inserts accordingly.

  3. 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

Databases: 2

Kindly suggest! Thanks..

  • To identify the procs with the most reads, execute SELECT * FROM sys.dm_exec_procedure_stats ORDER BY total_logical_reads DESC; – Dan Guzman Jan 19 '15 at 13:31
  • @DanGuzman It only says SP as a finding for a DB, which SP its executing, can we find or query its trying to execute ? – KASQLDBA Jan 19 '15 at 13:39
  • 2
    the individual queries are visible in sys.dm_exec_query_stats. – Dan Guzman Jan 19 '15 at 13:44
  • you are probably on track you must analyze the Stored procs that are kicked off by SQL Server agent – Shanky Jan 19 '15 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.