Sql-server – Can SQL Server prevent the pagefile from being moved

sql serversql-server-2008-r2

On a production server of ours the page file has grown to 30gb+. The Sys Admin has moved the Page File to a different drive, but for some reason the old one won't be removed. He thinks that SQL Server may be the culprit.

Could SQL Server prevent the page file from being moved?

Best Answer

I'd love to see a screenshot of the Virtual Memory applet, here's the one from my notebook:

enter image description here

SQL Server does not interact directly with the pagefile. Only the operating system itself is capable of doing that since the O/S holds an exclusive lock on the file while it is using it.

If SQL Server is the only load running on the machine (it should be for all but the simplest installations), and is correctly configured, the page file should be for most purposes irrelevant.

The SQL Server database engine has some very sophisticated memory management techniques, which can be managed by settings such as lock pages in memory, LargePageAllocator (see http://support.microsoft.com/kb/920093 for details), max server memory and min server memory, etc.

In my opinion, for a machine dedicated to SQL Server, I typically set the page file to a low amount, such as 4GB. I set max server memory to an amount close to the amount of physical ram in the machine (less than 94% of memory, leaving at least 2GB free), and set min server memory to the same amount. I then watch performance counters related to the page file, such as pages/sec etc, and SQL Server's Page Life Expectancy (see http://blogs.msdn.com/b/mcsukbi/archive/2013/04/12/sql-server-page-life-expectancy.aspx) to make sure SQL Server is not paging memory to disk. If SQL Server is paging to disk, or the Page Life Expectancy is low (this is the number of seconds a page is retained in the buffer pool on average) I will request that physical memory be increased in the machine.

For instance, on the last production machine I worked on, we had 192GB of RAM, and had the page file set to 4GB. The default setting Windows "suggested" would have been 288GB, a clearly ridiculous amount.

For further info on this topic, see:

https://dba.stackexchange.com/a/20492/10832

http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/

https://dba.stackexchange.com/a/10201/10832

Related Question