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.
Who is Participating?
TheCapedPlodderConnect With a Mentor Commented:
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.


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

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?
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.