Solved

Sql Server and Page file

Posted on 2011-09-07
8
142 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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 500 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

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!

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

691 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