March 6, 2012 at 2:52 pm
Hi,
I'm trying to understand why SQL Server has such a low page life expectancy. The range is between 5 seconds and 1 minute most of the time.
Running SQL Server 2005 Standard Edition on Windows Server 2003 R2 Standard Edition 32bit 4gb ram.
SQL Server on a dedicated server so aside from OS, there is no need to allocate ram for other applications. Task manager shows that SQL Server is using 1.5GB ram with lots of free memory which makes me wonder why it is evicting pages so quickly.
AWE is not enabled, min server mem is 0mb and max is 2147483647mb. Index creation mem is set to dynamic and min mem per query is 1024KB.
Any ideas?
March 6, 2012 at 3:04 pm
At 1.5Gb SQL is taking about the max for the buffer pool it is able to in your scenario.
Try setting the /3Gb switch in the boot.ini to allow SQL another potential 1Gb of memory
---------------------------------------------------------------------
March 6, 2012 at 4:22 pm
Thanks George, would this server benefit from "Lock pages in memory" or enabling AWE? Will enabling AWE have an affect on 32 bit servers with only 4GB ram?
March 7, 2012 at 2:33 am
PHXHoward (3/6/2012)
Thanks George, would this server benefit from "Lock pages in memory" or enabling AWE? Will enabling AWE have an affect on 32 bit servers with only 4GB ram?
Sure it would help. There's a great article here[/url] that describes in detail what lock pages in memory does and how it affects x86 and x64 systems.
In a few words: do it.
-- Gianluca Sartori
March 7, 2012 at 3:13 am
As you only have 4GB of memory and AWE is to enable memory usage above 4GB, AWE is not applicable here.
Unless you see messages in your SQL error log to the effect that your working set is being trimmed lock pages in memory is probably not necessary (and is not as 'clever' on windows 2003), but you can try it (it does require a SQL restart)
see here
---------------------------------------------------------------------
March 7, 2012 at 3:29 am
george sibbald (3/7/2012)
As you only have 4GB of memory and AWE is to enable memory usage above 4GB, AWE is not applicable here.Unless you see messages in your SQL error log to the effect that your working set is being trimmed lock pages in memory is probably not necessary (and is not as 'clever' on windows 2003), but you can try it (it does require a SQL restart)
see here
Ha! Thanks for correcting me, George!
I missed that the server had only 4GB installed.
-- Gianluca Sartori
March 9, 2012 at 2:14 pm
Gianluca Sartori (3/7/2012)
In a few words: do it.
That's two :hehe:
Re: AWE on 32-bit...I thought you had to enable it if you wanted to enable LPIM, which also requires a trace flag and a GPO privilege, but nonetheless that's how I understood it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 10, 2012 at 5:03 am
Its the other way round, you need LPIM enabled if you want to use AWE.
and the trace flag is to use LPIM with standard edition 64bit. See the blog I refer to in my post above
---------------------------------------------------------------------
March 10, 2012 at 6:50 am
Note: my comments pertain to 32-SQL Server Standard Edition running on 32-bit Server 2003 R2 with 4GB physical RAM and the /3GB switch enabled.
george sibbald (3/7/2012)
As you only have 4GB of memory and AWE is to enable memory usage above 4GB, AWE is not applicable here.
AWE can be used in servers with 4GB of memory or less, e.g. when using LPIM. I used it recently to solve a memory trimming problem. Did you mean to say PAE?
george sibbald (3/10/2012)
opc.three (3/9/2012)
Gianluca Sartori (3/7/2012)
In a few words: do it.That's two :hehe:
Re: AWE on 32-bit...I thought you had to enable it if you wanted to enable LPIM, which also requires a trace flag and a GPO privilege, but nonetheless that's how I understood it.
Its the other way round, you need LPIM enabled if you want to use AWE.
Please forgive me. That is indeed a fair distinction made to a poorly worded statement. We cannot enable AWE without the LPIM privilege in place however I pair them together so got loose with the wording. From this MSDN article it says "On 32-bit operating systems, setting [the LPIM] privilege when not using AWE can significantly impair system performance.".
and the trace flag is to use LPIM with standard edition 64bit.
Trace flag 845 is also required to gain LPIM capabilities in 32-bit Standard Edition (available since CU4 for SQL Server 2005 SP3).
@PHXHoward Here is what worked for me.
My specs:
- 32-bit Server 2003 R2 Standard Edition w/ SP2
- 4GB Physical RAM
- 32-bit SQL Server 2005 w/SP4
My symptoms:
- Periodic memory trims resulting in a SQL Error Log messages like "A significant part of sql server process memory has been paged out"
Steps:
1. Add /3GB switch to boot.ini, reboot server - this allows SQL Server to use up to 3GB physical RAM instead of only 2GB due to standard VAS split
2. Add trace flag 845 to SQL Server startup parameters. Do not restart SQL Server service yet, we'll get that on reboot in next step.
3. Grant LPIM privilege to SQL Server Service Account. Restart server.
4. Enable AWE in SQL Server using sp_configure. Restart server.
5. When SQL Server comes back look for an entry in SQL Error Log beginning with "Address Windowing Extensions is enabled." to know SQL Server is locking its pages in memory.
After doing all this I no longer see Windows trimming my SQL Server memory.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 10, 2012 at 8:21 am
I also failed to mention that the original symptom I was seeing that led me to go down the path of adding the 3GB switch and setting up LPIM was sudden drops in PLE. The sudden drops were sometimes due to spikes in database activity that caused major amounts of memory to be swapped out of the buffer pool as well as the aforementioned trimming issues. Before adding the /3GB switch you should confirm the # of free MBs on your system. I had ~1.4GB so I was comfortable adding the /3GB switch without the /USERVA switch to grab the whole extra 1GB.
Since making the changes I mentioned I still see periodic drops in PLE, but they are due to spikes in database activity and the lack of memory in the server, not memory trims. The server I mentioned is migrating to a 64-bit SQL 2008R2 environment with 12GB RAM soon to completely address the issue.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 10, 2012 at 9:02 am
opc.three (3/10/2012)
Note: my comments pertain to 32-SQL Server Standard Edition running on 32-bit Server 2003 R2 with 4GB physical RAM and the /3GB switch enabled.george sibbald (3/7/2012)
As you only have 4GB of memory and AWE is to enable memory usage above 4GB, AWE is not applicable here.AWE can be used in servers with 4GB of memory or less, e.g. when using LPIM. I used it recently to solve a memory trimming problem. Did you mean to say PAE?
nope. I seriously doubt you actually needed to configure AWE as well. It only allows 32bit SQL to address memory above the 4GB line, so serves no purpose in a 4GB system
---------------------------------------------------------------------
March 10, 2012 at 2:38 pm
george sibbald (3/10/2012)
opc.three (3/10/2012)
Note: my comments pertain to 32-SQL Server Standard Edition running on 32-bit Server 2003 R2 with 4GB physical RAM and the /3GB switch enabled.george sibbald (3/7/2012)
As you only have 4GB of memory and AWE is to enable memory usage above 4GB, AWE is not applicable here.AWE can be used in servers with 4GB of memory or less, e.g. when using LPIM. I used it recently to solve a memory trimming problem. Did you mean to say PAE?
nope. I seriously doubt you actually needed to configure AWE as well. It only allows 32bit SQL to address memory above the 4GB line, so serves no purpose in a 4GB system
You may be technically correct regarding "needed to". If I am understanding the VAS split correctly, and 32-bit SQL Server can access 4GB of RAM with no help from AWE, then even with the 3GB switch enabled SQL Server should not need AWE enabled to access all 3GB.
That said, on 64-bit SQL Server while the AWE option is ignored the AWE APIs are still used by SQL Server to lock pages in memory. Does it stand to reason that 32-bit SQL Server would want to use the AWE APIs to lock pages in memory as well, but that it could not unless AWE were explicitly enabled? I have not found anything definitive online that supports that claim but it is interesting nonetheless. The closest I came to finding something that supported this notion was this article from psssql which lays out the algorithm used to determine when a guiding message regarding LPIM and AWE should be written to the SQL Error Log. It jumps around a bit but from that article this seems to pertain to the use-case at hand:
The message "Set AWE Enabled to 1 in the configuration parameters to allow use of more memory" is printed if the size of the Virtual Address space is < the amount of physical memory on the machine (or the 'max server memory' option). So on a 32bit machine where /3GB is being used, if the machine has 4Gb of physical RAM, we will print this message if 'awe enabled' = 0.
All of that aside, at the end of the day I decided to enable AWE based on the cautionary statement in the MSDN article I linked to earlier regarding the potential for poor system performance on a 32-bit system with LPIM enabled and AWE disabled. The article did not specify whether the comment pertained to systems with 4GB or less, or only more than 4GB.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 13, 2012 at 2:48 pm
Thanks for all the great information.
The Microsoft rep that we have on site this week confirmed that enabling AWE and LPIM on the 4gb RAM 32bit system would allow SQL Server to use the extra gb for pages. He said it was like the boot.ini /3gb but without having to modify the boot.ini
March 13, 2012 at 3:00 pm
PHXHoward (3/13/2012)
Thanks for all the great information.The Microsoft rep that we have on site this week confirmed that enabling AWE and LPIM on the 4gb RAM 32bit system would allow SQL Server to use the extra gb for pages. He said it was like the boot.ini /3gb but without having to modify the boot.ini
Very interesting! Thanks for posting back. Once you make these changes and the buffer pool warms up would you mind running this on your system and posting the results?
SELECT CAST(physical_memory_in_bytes / (1024.0 * 1024.0 * 1024.0) AS DECIMAL(20, 2)) AS physical_memory_in_GB,
CAST(virtual_memory_in_bytes / (1024.0 * 1024 * 1024) AS DECIMAL(20, 2)) AS VAS_GB,
CAST((bpool_committed * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS buffer_pool_committed_memory_in_GB,
CAST((bpool_commit_target * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS buffer_pool_target_memory_in_GB
FROM sys.dm_os_sys_info;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 13, 2012 at 3:05 pm
I'd be happy to run that query. It will be a while though because the production system I'm looking at isn't scheduled for a restart until the end of this month.
FWIW, these two articles seem to agree.
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/11/awe-and-3gb-an-empirical-picture.aspx
http://www.devproconnections.com/article/database-development/enabling-sql-server-awe-memory
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply