Page History

...

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