?
Solved

Sql Server and Page file

Posted on 2011-09-07
8
Medium Priority
?
143 Views
Last Modified: 2015-03-26
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
Comment
Question by:Markisha1979
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
8 Comments
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36496083
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
 

Author Comment

by:Markisha1979
ID: 36496330
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
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36496620
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Markisha1979
ID: 36496700
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
 
LVL 21

Accepted Solution

by:
JestersGrind earned 2000 total points
ID: 36496778
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
 

Author Comment

by:Markisha1979
ID: 36515882
I'll continue to investigate.
I'll keep you up-to-date!
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

OfficeMate Freezes on login or does not load after login credentials are input.
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question