Solved

Pagefile setting for SQL 2005

Posted on 2008-06-25
5
834 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 78

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
Learn about cloud computing and its benefits for small business owners.

739 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