3

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?

|improve this question
  • 3
    Has the server been restarted? – Max Vernon Nov 1 '13 at 20:04
  • 3
    Have you tried deleting the original file? I don't believe it gets cleaned up automatically. Also, if the page file is that big, your server is overcommitted. A SQL Server should never be swapping to disk because it's out of memory. – Jon Seigel Nov 2 '13 at 15:50
  • 3
    @jon - the old pagefile should be cleaned up automatically; however there is no harm at all in attempting to delete it. In fact, attempting to delete it will result in one of two things: a) the file will be deleted which indicates the system is not using it, or b) "Error: the file is in use" will occur indicating the O/S (or some process such as an antivirus scanner) has the file open. – Max Vernon Nov 2 '13 at 16:55
  • 2
    @Max: I've seen some occurrences where it wasn't cleaned up, so I don't rely on that. So yes, exactly as you said, there's really no reason not to try to delete the original. – Jon Seigel Nov 2 '13 at 17:59
4

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

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.