September 30, 2009 at 9:34 am
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
September 30, 2009 at 10:01 am
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).
October 1, 2009 at 2:06 pm
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:
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
October 2, 2009 at 3:31 am
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.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply