3

For one of my servers with below configuration:

Total RAM: 32 GB

CPU: 8

Max Server Mem: 22.5 GB

Min Server:: 4 GB

Edition: Enterprise Edition with 2k8R2 installed on SP3

Page file: System Managed on the C:\

AWE Enabled: NO as it being 64 Bit

I am continuously getting the alerts for OS paging High..

When I checked using RAMMAP Tool for memory utilization, I found that there is AWE which using an approx of 23.8 GB and thus blocking any further memory usage:

More over for the user right assignments : "Locked pages in Memory" has not been assigned any rights!

Kindly help me understanding where more I need to investigate, or a solution for this

Thanks!

5
  • What is service account running SQL Server. What is output of select (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB, (locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB from sys. dm_os_process_memory
    – Shanky
    Dec 6, 2014 at 12:16
  • 23630 is memory by sql server 23100 is locked pages used by sql.. and the service used to run sql server is local system account..
    – KASQLDBA
    Dec 6, 2014 at 12:40
  • If SQL Server is running under Local system account by default SQL Server service account will get LPIM privilege. Its pretty much normal. Where are you getting OS paging high what is page file size. As a general recommendation it should be 1.5 times RAM on system but you need to see perfmon counter to get accurate value
    – Shanky
    Dec 6, 2014 at 12:43
  • I am getting the OS paging high alerts from Idera Diagnostic Manager. Yes the page file used is checked with option of "System managed"
    – KASQLDBA
    Dec 6, 2014 at 13:08
  • yes tools can give some information like that I have updated my answer please can you read and see what is value of page file and set proper value
    – Shanky
    Dec 6, 2014 at 13:22

1 Answer 1

3

am continuously getting the alerts for OS paging High.

Memory is basically two types the physical random access memory (RAM) and virtual memory. A process has to be first mapped to Virtual memory and then to physical memory. All programs use RAM, but when there isn't enough RAM for the program you're trying to run, Windows temporarily moves information that would normally be stored in RAM to a file on your hard disk called a paging file. The amount of information temporarily stored in a paging file is also referred to as virtual memory. Now error page file is less can have multiple meanings

  1. You are running some processes which is heavily utilizing memory and since OS cannot find enough RAM for it it has to use Page file as temporary area for storage.

  2. Since SQL Server is running with Locked pages in memory privilege this memory cannot be paged out to disk and OS cannot directly used this memory by asking SQL Server to release it heavily. So in this case you need to lower max server memory of SQL Server a bit and give more memory to OS so that it can function properly. use how to set optimum value for max server memory to set correct value for max server memory. In your case you have left 10 G for OS which I consider is pretty much good unless you are using SSIS,SSAS and SSRS.

Problem may be not SQL Server memory or memory allocated by AWE API( Locked pages) problem here is windows page file size is less. You need to act on this. Page file is temporary area which is used to hold temporary data which is swapped in and out of physical memory in order to provide a larger virtual memory set. Page file space is reserved when the pages are initially committed, however the page file locations are not chosen until the page is written to disk.

The page file needs of an individual system will vary based on the role of the server, load etc. There are some performance counters that you can use to monitor private committed memory usage on a systemwide or per-page-file basis. There is no way to determine how much of a process' private committed memory is resident and how much is paged out to paging files.

Memory: Committed Bytes: Number of bytes of virtual memory that has been committed. This does not necessarily represent page file usage - it represents the amount of page file space that would be used if the process was completely made nonresident

Memory: Commit Limit: Number of bytes of virtual memory that can be committed without having to extend the paging files.

Paging File: % Usage Percentage of the paging file committed

Paging File: % Usage Peak Highest percentage of the paging file committed

Please use above counters to set proper value for page file. You can read This Link to get more information about page file

You can set page file by Right click on My Computer select properties and then go on Advanced System Settings.In Performance section click on setting and then advanced tab. You would see virtual memory section and then click on change you can set virtual memory here. Please refer to screenshot. Take help of windows team in configuring optimum value. If option automatically set page file for drives is set you can uncheck it and click on SET to set optimum value of page file.

enter image description here

When i checked using RAMMAP Tool for memory utilization i found that there is AWE which using an approx of 23.8 GB and thus blocking any further memory usage:

This is pretty much normal considering the fact that SQL Server service account is Local system so by default SQL server account will get Locked pages in memory privilege.

7
  • Thanks for a nice and clean explanation and currently we are on the same page. I have analysed the system and attached is the result analysis without much change in values.
    – KASQLDBA
    Dec 6, 2014 at 13:51
  • As per screenshot committed is less than commit limit but remember value shown is instantaneous please monitor it for couple of days. Commit limit is 27 G which seem normal. Also note sometimes these tools can give unnecessary messages so they are always not threatening. Please speak to windows team about the same. Ask them whether they have set page file on drives as well. Can you look for current value of page file and tell me
    – Shanky
    Dec 6, 2014 at 15:20
  • Hello @ Shanky, i did msinfo32 on system to check the location of pagefile and it says its location on C:\. with size of 32 GB.
    – KASQLDBA
    Dec 8, 2014 at 10:36
  • imageshack.com/a/img537/3307/4p7dhJ.png for latest page file use
    – KASQLDBA
    Dec 8, 2014 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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