Page File for SQL Server

Page File for SQL Server

Page file is space allocated on system drive as a file and its functionality is: operating system works with memory and paging file. The page file is located on the disk, and when there is not enough physical memory left, operating system can use the page file and move data from the physical memory to it. When it will need that data, it will have to read it from the disk. The problem is that disk is a lot slower than the physical memory. If SQL Server's cache will be stored on the disk instead of the on the physical memory, you'll have a huge performance problem.

In general page file is physical extension of RAM and if not enough memory left during process page files save server from crash or being hung out.

Maximum memory space required by windows commit limit will be: RAM size + page file size

Normally a DBA set page file to 2X of RAM size which is not always correct and it depends over server functionality. For 32 bit size maximum page size should be 4 GB.

But for modern server we should have less page file size if RAM size is more, we can use performance counter to know the value of page file.

Create counter Memory\Committed Bytes and set it for one week and once we have data calculation would be

Max value if committed byte over period (20 GB) + 30% of committed byte as buffer – RAM Size.

For 16 gb Ram = data would be 20+6-16 = 10 gb pf page file

+  we also need to consider kernel or memory dump size which also store data primarily on page file before moving it to specified dump folder.

From Windows 2008 and onward we can put page file on any partition, not only system partition as before 2008. If page are created along multiple drives sql will generally use page file from faster drive.

To configure page file setting: go to:

 system->advanced system setting->advanced->setting under performance tab->Advanced