Solved

Pagefile setting for SQL 2005

Posted on 2008-06-25
5
832 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
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 77

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can’t delete a file 14 163
Table create permissions on SQL Server 2005 9 42
Help with SQL - TOP 10 by date and by group 13 40
HP Printer on Windows 2003 Terminal Server 4 36
A quick step-by-step overview of installing and configuring Carbonite Server Backup.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

803 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