Solved

Pagefile setting for SQL 2005

Posted on 2008-06-25
5
835 Views
Last Modified: 2013-11-10
We have a new SQL 2005 running on a MS 2003 server.  There is 8 Gigs of physical RAM in the box and I have a dedicated drive for the pagefile.  My question is what should I set the pagefile at for "Initial Size" and "Maximum Size"?  I've read before that setting both at RAM + (RAM * 1.5) would keep the pagefile from becoming fragmented.  Also are there other requirements that I should take into account for SQL?  If my thoughts are correct I should set the Initial size at around 12 Gigs and set the maximum at around 16 Gigs?  The drive dedicated for the pagefile is 80 Gigs so that shouldn't be a problem.
0
Comment
Question by:taltomare
[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
5 Comments
 
LVL 9

Expert Comment

by:the_b1ackfox
ID: 21864891
I have always been taught two things.  1  Set the page file to the same initial and max size.  I haven't seen it grow like it is suppossed to...  and 2)  if you have multiple drives, put a page file on each drive
0
 
LVL 13

Expert Comment

by:TheCapedPlodder
ID: 21864976
Agree with the Blackfox.

We have a 2048MB min and max on C: and 4096MB min and max on the dedicated pagefile drive.

If your server needs more than 6GB of pagefile then it is memory bound and would benefit from more physical memory although I've never seen a server use more than 6GB.

As an aside, have you configured AWE and PAE?

TCP
0
 

Author Comment

by:taltomare
ID: 21865094
I have added the PAE switch to the boot.ini file.  I'm not up to speed on AWE.  I imagine it has to do with SQL and memory usage?
0
 
LVL 79

Expert Comment

by:arnold
ID: 21865127
Make sure to configure the server as an application server.  properties of my computer/advanced/performance settings/advanced the settings should reflect processor scheduling programs, memory usage programs.

You do not want your system to page/swap since that will reduce the performance of the system. (acccess to swap/page file on the disk is much slower then access to memory)  If your system is paging/swapping add more physical memory.

AWE if you are not using x64bit sql/w2k3.
http://msdn.microsoft.com/en-us/library/ms175581.aspx
0
 
LVL 13

Accepted Solution

by:
TheCapedPlodder earned 250 total points
ID: 21878348
SQL 2005 configures the performance settings correctly when you install it but it is definitely worth checking.

AWE can be enabled quite easily in SQL 2005 open the properties of the server from Management Studio and under memory enable AWE and set the max and min settings.  With 8GB physical I would recommend an AWE min of 1GB and a maximum of 6GB.

You may need to add the user account that the SQL server service runs under to the Lock Pages in Memory right in gpedit.msc.

Cheers,

Plod
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

On July 14th 2015, Windows Server 2003 will become End of Support, leaving hundreds of thousands of servers around the world that still run this 12 year old operating system vulnerable and potentially out of compliance in many organisations around t…
Arrow Electronics was searching for a KVM  (Keyboard/Video/Mouse) switch that could display on one single monitor the current status of all units being tested on the rack.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

617 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