none
significant part of sql server process memory has been paged out. RRS feed

  • 问题

  • I just installed a new SQL Server instance and currently no user database is running. When i checked SQL Server error log, lots of information shows 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
    environment:

    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 memory 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.


    Anybody encontered such problem before? please advise. thansk!


    Please Mark As Answer if it is helpful.

    2014年8月18日 1:44

答案

  • found one article SQL Server reports working set trim warning message during early startup phase .Last line mentions this can be seen on servers with no user databases:

    On a server with no databases to recover and no activity, it is quite possible to see the above behavior. So before spending a lot of time investigating why this is happening, please look at the values for the Working Set and committed memory.

     

    So currenly i will just ignore this warning and check after user database running.


    Please Mark As Answer if it is helpful.

    2014年8月18日 8:55

全部回复

  • Take look at this blog: http://mssqlwiki.com/2012/06/27/a-significant-part-of-sql-server-process-memory-has-been-paged-out/
    2014年8月18日 2:00
  • I have read this article and configure the LowMemoryThreshold  values. But i still can't figure out why windows sending memory low notification. Based on article: Using sys.dm_os_ring_buffers To Diagnose Memory Issues in SQL Server,the value of IndicatorsSystem means:

    Value

    Meaning

    1

    High Physical Memory

    2

    Low Physical Memory

    4

    Low Virtual Memory

    then i query my system:

     the IndicatorsSystem values is 2, this means "Low Physical Memory". But from the screeshot, we can seee about 121GB memory is free. It seems windows sending wrong data. Not sue whether this is related with hardware.


    Please Mark As Answer if it is helpful.



    2014年8月18日 3:05
  • found one article SQL Server reports working set trim warning message during early startup phase .Last line mentions this can be seen on servers with no user databases:

    On a server with no databases to recover and no activity, it is quite possible to see the above behavior. So before spending a lot of time investigating why this is happening, please look at the values for the Working Set and committed memory.

     

    So currenly i will just ignore this warning and check after user database running.


    Please Mark As Answer if it is helpful.

    2014年8月18日 8:55