1

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?

  • 1
    Does SQL server service account has Locked pages in memory privilege. That is very much required due to serious bug in Windows server 2003. Can you see errorlog and fine SQL server memory being paged out message ? – Shanky Sep 5 '14 at 15:45
  • Bingo. Found this in the current error log. No messages indicating paging however. SQL Server is not configured to use all of the available system memory. To enable SQL Server to use more memory, set the awe enabled option to 1 by using the sp_configure stored procedure. Lock Pages in Memory also has no service account membership. – MattyZDBA Sep 5 '14 at 15:52
  • 1
    With just 4 G of RAM no need to enable AWE. it will affect OS.Also apply Sp3 for SQL Server 2008. – Shanky Sep 5 '14 at 15:56
1

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.

  • It should recover but on 32-bit Windows Server 2003 and on a no-longer-supported SQL Server 2008 service pack, who knows. – Aaron Bertrand Sep 5 '14 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.