Sql Server and Page file

Hi all.
I look through the site looking for some help in solving a doubt related to paging file configuration and I find a couple of threads very helpful.

I reach this link http://support.microsoft.com/kb/889654 but I really need help interpreting the situation...

I have a sql 2008 onto a windows 2008 R2 server (both x64, of course), running in a virtual machine configured with 4 cpus and 16 GB ram.

In the article MS sayd that "as the amount of RAM increases, the need of a pagefile decreases".
16 GB ram is a big quantity for that kind of sql server (it contains 10 DB for a total of 500GB data, 100 users max) so I suppose that the page file shouldn't be very large (maybe even set to zero...) but I dig a bit using performance monitor and i found a strange behaviour.

extract from article:
Memory\\Available Bytes                        No less than 4 MB
Memory\\Pages Input/sec                        No more than 10 pages
Paging File\\% Usage                        No more than 70 percent
Paging File\\% Usage Peak                No more than 70 percent
Process\\Page File Bytes Peak                Not applicable

My environment

Memory\\Available Bytes                        2GB (I configured sql to use 12GB)
Memory\\Pages Input/sec                        average 250!!! (max 700!!!)
Paging File\\% Usage                        4%
Paging File\\% Usage Peak                5%
Process\\Page File Bytes Peak               16 GB (related to _total counter)

So, I really don't understand this behaviour: the percentage of page file used convinced me that I really don't need a big page file (surely not 1,5 times the amount of ram) but I don't understand the data related to pages input/sec.

Someone could help me???
Who is Participating?
JestersGrindConnect With a Mentor Commented:
I understand and unfortunately the answer is, it depends.  You are on the right track though.  You are monitoring the right counters.  Your page file usage is very low, so you could probably use a smaller pagefile than what you have.  I've even heard some people suggest that x64 systems don't need a pagefile at all.  I don't know if I believe that entirely, but I've had the luxury of working on systems where storage space wasn't an issue.  Occasionally when a system doesn't have a very big C:\ drive, I'll put the pagefile on another drive.


Memory\\Pages Input/sec needs to be used in conjunction with Memory\\Page Faults/sec.  A page fault occurs when data is not in the memory location that the application expects it to be, so it has to look for it some where else.  

Pages Input/sec / Page Faults/sec = % Hard Page Faults

If the above % is greater than 40%, that indicates that you do not have enough RAM in the server.  

16GB might sound like a lot of RAM, but when you have 500GB of SQL data, you obviously can't store all the data in RAM for easy retrieval.  Most likely much of the data doesn't need to be retrieved regularly, but the above will tell you if you're having memory issues.



Markisha1979Author Commented:
I understand what you're saying but my question is related to page file config.

Are you saying that if I have a percentage of hard page fault minor than 40% I don't need a pagefile?
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

I wouldn't say that you don't need a pagefile at all.  I've always followed the 1.5 x RAM standard.  The pagefile is a file that handles situations where the system wants to move data out of memory.  Being out of memory is probably the most common one.  So, it stands to reason that the more RAM that is in a server, the less of a need there is for a pagefile.  That being said, you could probably get away with a smaller pagefile if you have storage concerns, but you do need some sort of pagefile.


Markisha1979Author Commented:
My goal isn't to completely delete my page file, but I don't want to trash a lot of space without a good reason...
I'm asking help to decide what size should be adequate.
Markisha1979Author Commented:
I'll continue to investigate.
I'll keep you up-to-date!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.