• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 147
  • Last Modified:

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???
0
Markisha1979
Asked:
Markisha1979
  • 3
  • 3
1 Solution
 
JestersGrindCommented:
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.

http://technet.microsoft.com/en-us/library/cc768048.aspx

Greg

0
 
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?
0
 
JestersGrindCommented:
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.

Greg

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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.
0
 
JestersGrindCommented:
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.

Greg

0
 
Markisha1979Author Commented:
I'll continue to investigate.
I'll keep you up-to-date!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now