Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 839
  • Last Modified:

Pagefile setting for SQL 2005

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
taltomare
Asked:
taltomare
1 Solution
 
the_b1ackfoxCommented:
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
 
TheCapedPlodderCommented:
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
 
taltomareAuthor Commented:
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
 
arnoldCommented:
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
 
TheCapedPlodderCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now