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:

  • On RAID 5: ((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.
  • 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 writes

If the page writes rate exceeds 65 for more than five seconds every 20 minutes, then consider the following:

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



IDERA | Products | Purchase | Support | Community | Resources | About Us | Legal