Solved

Sql Server and Page file

Posted on 2011-09-07
8
140 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

821 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