Paging File size

I hope these are pretty basic SQL Server management questions. I'm running a SP that uses a lot of memory. The server has about 8GB and it uses 7.95GB. When the memory usage goes up, so does the size of the paging file. I'm assuming the page file grows so it can write the memory contents to disk in the case of a crash. How necessary is this? It seems like any advantage I'd gain from working out of memory is lost because I'm working off the drive too.

Can I/should I turn this "feature" off?

Thanks!

Brian

Paging file is used to swap files quickly in and out of RAM. The system can't possibly keep everything in RAM that it ever needs. So it swaps in use files back and forth between RAM and the pagefile as needed. It's a secondary backup to RAM, sort of.

The paging file is growing because if SQL Server is using 7.95 GB of RAM of 8 available, there's not enough RAM left for the operating system to function, and it is forced to rely on the paging file for its normal operations.

A general rule of thumb for a SQL Server is that the paging file should be at least double the amount of RAM. So if you have 8 GB of RAM, the paging file should be 16 GB ... a preferably on a different disk than any of the SQL files. Set the paging file to a hard size, don't let Windows manage your paging file.

If your server has 4 or more GB of RAM, you should reserve at least 1 GB of RAM for the OS. You do this by setting the maximum amount of memory that SQL Server can use to the total amount - 1 GB. So for 8GB of RAM, set SQL to use a maximum of 7 GB.

If 7.95 GB of RAM are being used though not necessarily by SQL Server, the OS may be forcing SQL Server to give up memory and forcing it to page. Make sure that SQL Server has been granted "Lock pages in memory" rights in the group policy editor.

|||

Robert,

The thing that bothers me is the files grow simultaneously. They both start out below 1GB and both grow at approximately the same rate. That is why I wondered if Windows was preparing for a memory dump by keeping an "Image" of memory in case of a crash. I haven't used this setting since XP, but assume it's still around in 2003. Although I think it's disabled by default.

Whatever the case, I'll assume that the behavior is normal for now. I have implemented a couple of your suggestions.

Thanks for the help.

Brian