1

Around 7:30pm every evening we receive the following message and the server experiences an outage of 2 minutes.

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1868 seconds. Working set (KB): 2096696, committed (KB): 18715612, memory utilization: 11%.

The message appears in the log 4 times around a 5 minute period.

I have been investigating, and we do not have 'lock pages in memory' activated. Also, the min server mem is 0 (default) and max server memory set at around 26GB.

Nothing seems that unusual when looking at perfmon counters ie: Available MB, Free Pages, Page Life, Lazy Writes.

Its possible that there is memory pressure as a result of backing up our DBs around this time.

How can I better check what the problem is / rectify the situation?

Edit: Results of

select TOP 10 
    (sum(pages_kb)*128)/1024 as 'sizein_mb',
    type as 'clerkttype' 
from 
    sys.dm_os_memory_clerks 
group by 
    type 
order by 
    (sum(pages_kb)*128)/1024 desc

1903846  MEMORYCLERK_SQLBUFFERPOOL  
428635  CACHESTORE_SQLCP  
148648  CACHESTORE_OBJCP  
22371   MEMORYCLERK_SOSNODE  
16802   USERSTORE_SCHEMAMGR  
6834    OBJECTSTORE_LOCK_MANAGER  
5494    CACHESTORE_PHDR  
5246    MEMORYCLERK_SQLSTORENG  
4513    USERSTORE_DBMETADATA  
4168    MEMORYCLERK_SQLGENERAL  
  • I don't know why DB backups would cause significant memory pressure. The transfer buffers are tiny. – usr Mar 23 '16 at 15:26
  • 2
    Is this SQL Server running in a VM or on a physical machine? Also, why is lock pages in memory not turned on? Or are you planning to turn it on? – Max Vernon Mar 23 '16 at 15:33
  • 1
    Have you tried querying sys.dm_os_ring_buffers to see if sql server was experiencing memory pressure ? Are there any memory dumps created ? check - sys.dm_server_memory_dumps. Remember that LPM will protect only buffer pool. – Kin Shah Mar 23 '16 at 15:33
  • 1
    @AMorton1989 Can you check if they are when sql server logged the message ? How many databases are on the instance and how big they are ? We need more info to help you. – Kin Shah Mar 23 '16 at 15:42
  • 1
    Can you also run this query and add to question to see memory by clerks. select (sum(pages_kb)*128)/1024 as 'sizein_mb',type as 'clerkttype' from sys.dm_os_memory_clerks group by type order by (sum(pages_kb)*128)/1024 desc – TheGameiswar Mar 23 '16 at 15:45

Your Answer

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

Browse other questions tagged or ask your own question.