|
|
|
SSC-Enthusiastic
 
Group: General Forum Members
Last Login: Today @ 11:17 AM
Points: 139,
Visits: 204
|
|
SQL Server 2005 SP3 Standard Edition on Windows 2003 (Enterprise Edition SP2 32-bit) Primary database is 17GB.
My server was running with 4 GB of memory, and SQL Server consistently topped out at a bit over 1.7 GB used, as measured by the "Mem Usage" column of Windows Task Manager. We added the /3GB switch to the boot.ini file, rebooted, and SQL Server started to use a bit over 2.7 GB. To boost performance more we added another 2 GB of real memory to the server, and replaced the /3GB switch with the /PAE switch. After running for a while SQL memory usage was only 1.7 GB.
So I started the Group Policy Editor and gave "Administrators" the "Lock pages in memory". SQL Server is logging on using the local system account which is in the Administrators group. Now SQL Server memory, after running a few hours, is only 81KB. Performance is not bad, though usage is light today.
Any idea what I'm missing here? How can I help SQL Server use the memory we've installed?
Thank you for any assistance -
Alan
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Wednesday, February 17, 2010 9:54 AM
Points: 63,
Visits: 286
|
|
6GB is pretty less memory for Production servers, but I guess you got to make do with what you got.
You already have /pae set up which will help OS see memory beyond 4GB.
With only 6GB you can't do much here as OS also needs memory. What you can do is enable AWE, good that you gave 'lock pages in mem' to sql service acct. Set max mem for SQL to 4GB, leaving 2GB for OS. You can think about having the /3GB switch set up again if you want SQL to get more mem (Direct Memory Access of sql to 3GB).
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 11:17 AM
Points: 139,
Visits: 204
|
|
Years ago I took over managing a database system (Ingres 6) which performed poorly. It had been allowed to use too little memory and was missing patches. After updates and memory tuning one query ran in 10% of the previous time. I've always felt that software, and people, need space to breath.
Turns out that the /PAE switch is working fine. Windows Task Manager doesn't see AWE memory, and SQL Server uses AWE first when enabled. The best overall explanation I found was Ajmer Dhariwal's blog:
How to manage memory -
http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/
To get a handle on real usage DBCC is the easiest tool to use. But most of the data leave you with no options to change them (which is a good thing).
Understanding DBCC MEMORYSTATUS - KB907877
http://www.mskbarticles.com/index.php?kb=907877
Alan
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 3:49 AM
Points: 2,010,
Visits: 1,647
|
|
If your server has 6GB memory, then probably the best option is to use both the /PAE and /3GB switches in boot.ini, and turn on AWE in SQL Server.
If you have data execution protection enabled, then PAE is also enabled, but it does no harm to provide a /PAE switch in boot.ini
The /3GB switch is safe to use on a 32-bit server with up to 12 GB memory. Betwen 12 and 16 GB it can be used, but you should also use the /USERVA switch with an appropriate value to ensure Windows has enough memory. Above 16 GB then /3GB should not be used.
The /3GB switch will limit Windows to using only 1GB memory, allowing applications (such as SQL Server) to use the other 3GB of memory below the 4GB line. If you do not have the /3GB switch then applications can only use 2GB of the memory below the 4GB line.
AWE improves memory management on all editions of SQL Server 2005 and above as discussed in http://blogs.msdn.com/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx, and I recommend it is always used (apart from Express where AWE is not supported).
If you enable all of these things, you should be able to set SQL max memory to somewhere between 4 and 4.5 GB without risking memory pressure. When you set max memory, take into account all the non-SQL tasks that are running on the box and leave room for them.
Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2005, 2008, and 2008 R2. 24 February 2010: now over 9,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
|
|
|
|
