This metric counts the number of physical reads the disk sub-system performs to bring SQL Server database pages into the data (buffer) cache. For the first few minutes after you start or restart SQL Server, almost any request for data is retrieved from disk. As the data cache fills, these requests are reduced. This value is measured by reading 'PAGE READS/SEC' from sysperfinfo.

Currently, even the fastest disks can support a maximum sustained rate of around only 85 random IOs per second. To determine if your IO subsystem is "flooded," perform the following calculation:

On RAID 10: ((Page reads + read ahead pages + ((page writes + checkpoint pages + lazy writes + log flushes) X 2)) / number of disk spindles in RAID array) must be < 65.

Reduce page reads

If the page reads rate exceeds 65 for more than five seconds every 20 minutes, then consider these solutions:

  • Add more physical memory (RAM) to the computer.
  • If your site makes use of extended stored procedures that are not called very often, then after calling them, issue a DBCC DLLNAME (FREE). Once an extended stored procedure is called, it remains in memory until SQL Server is shut down even if it is never called again. This is tremendously wasteful in terms of available memory and a DBCC DLLNAME (FREE) releases that memory for use by both the procedure cache as well as the Data Pages which have a significant positive effect on both the Procedure Cache Hit Ratio as well as the Buffer Cache Hit Ratio. In order to view the extended stored procedures currently loaded in memory by SQL Server, execute SP_HELPEXTENDEDPROC.
  • Allow SQL Server to consume more of the available memory (making sure that OS Paging does not increase).
  • If you are running large, batch-style reporting queries on the OLTP server, then large numbers of data pages are flushed from cache causing much additional IO overhead. In this situation, set up a separate SQL Server Reports computer to service all but the smallest reports. You can feed this Reports Server via backups and Log Shipping from the OLTP server.
  • Stop unnecessary programs (such as the Print Spooler or activities such as the database server acting as either a BDC or PDC) from running.
  • If the computer is running multiple instances of SQL Server, then consider placing each instance on a separate physical computer.

SQL Diagnostic Manager identifies and resolves SQL Server performance problems before they happen. Learn more > >