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.
If the page reads rate exceeds 65 for more than five seconds every 20 minutes, then consider these solutions:
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
.SQL Diagnostic Manager identifies and resolves SQL Server performance problems before they happen. Learn more > >
|