August 17, 2014 at 7:31 pm
I just installed a new SQL Server and currently no user database running. When i checked SQL Server error log, i found lots of errors like "memory has been paged out".
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 8984, committed (KB): 4294248, memory utilization: 0%.
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 328 seconds. Working set (KB): 105088, committed (KB): 323168, memory utilization: 32%.
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 657 seconds. Working set (KB): 105280, committed (KB): 322712, memory utilization: 32%.
Below is my server configruation:
1.Window Server 2008R2 Enterprise SP1 Version:6.1.7601 Service pack1 build 7601
2.Total Server memory: 128GB
3.SQL Server Version: Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)
4.SQL Server max server memory 24GB
5.Lock pages in memory has been configured
In my opnition,SQL Server page out only when windows encontered problem ask SQL Server to relase or SQL Server has no enough memory has to swap data to virtual memory. But in my case, only total 6.7GB was used. windows and SQL Server has lots of avaliable memory. Why SQL Server still page out?
In addtion, I have captured windows performance counter about process working set and private bytes, these values seems no difference between normal time and problem happened.
Please advise thanks!
August 18, 2014 at 1:07 am
Is it SQL Enterprise edition?
Run this select * from sys.dm_os_process_memory
Is there a non 0 value for locked_page_allocations?
August 18, 2014 at 1:19 am
the locked_page_allocations_kb values in sys.dm_os_process_memory is 0
August 18, 2014 at 1:26 am
In addtion, i query the sys.dm_os_ring_buffers :
TypeIndicatorsProcessIndicatorsSystemAvail Phys Mem, KbAvail VAS, Kb
RESOURCE_MEMPHYSICAL_LOW021276674328455055332
RESOURCE_MEMPHYSICAL_LOW221276677728455057380
the value of IndicatorsSystem is 2, this means Low Physical Memory. But you can see the Avail Physical Memory is more than 120GB. Actaully this server didn't have memory pressure. Why windows still notifiy SQL Server to page out?
August 18, 2014 at 1:38 am
I'd say you need to restart the SQL instance. Also is it Enterprise Edition?
August 18, 2014 at 2:31 am
Possibly safe to ignore. See http://blogs.msdn.com/b/psssql/archive/2009/05/12/sql-server-reports-working-set-trim-warning-message-during-early-startup-phase.aspx
Last line mentions this can be seen on servers with no user databases
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2014 at 2:48 am
Thanks. I will ignore this warnning and check after install user database.
August 18, 2014 at 2:48 am
It is a standard version and tried to reboot the server, still has this warning.
August 18, 2014 at 3:04 am
Did you read the blog post I referenced?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2014 at 3:38 am
Trace flag 845 needs to be enabled in standard edition for LPIM to work.
August 18, 2014 at 4:45 am
Andrew G (8/18/2014)
Trace flag 845 needs to be enabled in standard edition for LPIM to work.
Locked pages may not be required here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply