I came across an unusual scenario on an instance where only half the physical RAM was being utilized but I was observing PLE values between 1-3 seconds constantly.
Windows Server 2003, Enterprise 5.2.3790 (32-bit) SP2
4 GB RAM
2 Xeon 2.3 GHz CPU
SQL Server 2008 SP2 (10.0.4000)
Min/Max Server Memory were set at defaults and the most resource intensive query was the backup history check by Quest Spotlight. Only 1.6 GB of RAM was utilized, 1.5 of which by buffer and procedure cache.
After setting Max Server Memory to 3 GB, PLE started to climb and is currently at 21 minutes.
I am wondering why Max Server memory would have this positive impact on PLE when less than half the onboard RAM is currently utilized. My first thought is O/S memory trimming was in play before the change in the setting but how can I confirm this?
Best Answer
I'd suspect most of the memory was in use by the procedure cache or another component before you set the maximum memory limit. When you change the maximum memory limit, caches are flushed (you'll see this in the error log), which would have made more space available for caching data pages, which would in turn improve PLE.
If that is what happened, the problem will likely return in time as usage from the components that were using memory before the flush grows back. As you may know, Glenn Berry has some useful queries for analysing memory usage in SQL Server.
As somewhat of an aside, 1.6 or 1.7 GB was roughly the maximum memory 32-bit SQL Server could use on 32-bit Windows under a default configuration. There are some settings that can allow it to use more memory, but they can cause memory starvation of the kernel or user processes if not carefully managed. On newer, 64-bit versions of Windows and SQL Server, memory management is generally far less problematic.