This metric counts the number of physical database writes the disk sub-system performs. 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:
((Page reads + read ahead pages + ((page writes + checkpoint pages + lazy writes + log flushes) X 4)) / number of disk spindles in RAID array)
must be < 65. ((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 writes rate exceeds 65 for more than five seconds every 20 minutes, then consider the following:
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 has 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 > >
|