The Page File

The page file is a disk file that the computer uses to increase the amount of physical storage for virtual memory. In other words, when the memory in use by all of the existing processes exceeds the amount of available RAM, the Windows operating system takes pages of one or more virtual address spaces and moves them to the page file that resides on physical disk. This frees up RAM for other uses. These “paged out” pages are stored in one or more page files that are located in the root of a disk partition. There can be one such page file on each partition.

                                    On a server running SQL Server, the objective is to try to keep SQL Server running using just the available physical memory. SQL Server itself goes to great lengths to ensure that it doesn’t over-allocate memory, and tries to remain within the limits of the physical memory available.

Given this basic objective of SQL Server, in most cases there is limited need for a page file. However, a frequently asked question is “Is there a recommended size for the page file?” The answer to this question, of course, is “it depends.” It depends on the amount of RAM installed and what virtual memory will be required above and beyond SQL Server. A general guideline is to configure a page file total of 1.5 to 2 times the amount of RAM installed in the server.

However, in large systems with a large amount of RAM (more than 128GB), this may not be

possible due to the lack of system drive space. Some good guidelines to adhere to in these cases are outlined in the following:

·         Configure an 8GB page file on the system drive.

·         Make sure that the Windows operating system startup parameters are configured to capture

A kernel dump in the event of a failure. Please see this article from Microsoft Support on how to configure this setting: http://support.microsoft.com/kb/307973.

·         Optional: Configure multiple page fi les (on disk volumes other than the system volume) that will be available for the OS to utilize if a larger page file is desired. It is recommended to use one additional page file of 200GB for each 256GB of RAM installed in the server.

In some cases, SQL Server and the OS might not cooperate well on sharing the available memory,

and you may start to see system warnings about low virtual memory. If this occurs, you will ideally add more RAM to the server, reconfigure SQL to use less memory, or increase the size of the page file. It may be better to reconfigure SQL Server to remain within the available physical memory than it is to increase the size of the page file. Reducing paging always results in better performance. If paging occurs, for best performance, the page file should be on fast disks that have minimal disk usage activity, and the disks should be periodically defragmented to ensure that the page file is contiguous on the disks, reducing the disk head movement and increasing performance. The metric in the Windows System Monitor to measure page file usage is Paging file: %Usage, which should be less than 70 percent.