none
Please interprete (make sense of) SQL Server log record. RRS feed

  • Dotaz

  • "A significant part of sql server process memory has been paged out. This may result in performance degradation. Duration 1842 seconds. Working set (KB) 133952, committed (KB) 342664, memory utilization - 39%"

    I've got quite a few records like this in the log file. What does it all mean? How to make sure this deficiency is corrected?

    Thanks, - MyCatAlex

    pátek 22. listopadu 2019 18:22

Odpovědi

  • it means something else is going on in your system that takes memory if available.

    SQL Server by default is cooperative. It monitors system memory. If low, it will give up memory. 

    was the above a single message? and system memory is currently 39%

    this might mean that whatever took memory gave it back, hence 61% unused.

    Year ago (I don't know about now), Windows Server would allocate memory for file caching. If you read a file, it would be put in the buffer cache. Sometimes to stupid effect. I might copying a backup file to a network location with no intent of ever reading that file (on the local system) again. But by default, Windows (task manager) will show: xx GB Cached.

    So is SQL Server important on this system, or should it cooperate? If SQL Server is important, think hard about how much memory it should have, and tough luck to any other process, then set "min server memory (MB)" appropriate. You could also be more draconian and use the Lock Pages in Memory , but you need to be absolutely sure this is what you intend to do this


    jchang

    • Označen jako odpověď MyCatAlex pátek 22. listopadu 2019 19:23
    pátek 22. listopadu 2019 18:49

Všechny reakce

  • it means something else is going on in your system that takes memory if available.

    SQL Server by default is cooperative. It monitors system memory. If low, it will give up memory. 

    was the above a single message? and system memory is currently 39%

    this might mean that whatever took memory gave it back, hence 61% unused.

    Year ago (I don't know about now), Windows Server would allocate memory for file caching. If you read a file, it would be put in the buffer cache. Sometimes to stupid effect. I might copying a backup file to a network location with no intent of ever reading that file (on the local system) again. But by default, Windows (task manager) will show: xx GB Cached.

    So is SQL Server important on this system, or should it cooperate? If SQL Server is important, think hard about how much memory it should have, and tough luck to any other process, then set "min server memory (MB)" appropriate. You could also be more draconian and use the Lock Pages in Memory , but you need to be absolutely sure this is what you intend to do this


    jchang

    • Označen jako odpověď MyCatAlex pátek 22. listopadu 2019 19:23
    pátek 22. listopadu 2019 18:49
  • was the above a single message? and system memory is currently 39%

    Thank you for interesting analysis. I will copy it to my personal database. Yes, I have dozens, perhaps hundreds of similar records with different percentages every time, though.

    - - MyCatAlex


    • Upravený MyCatAlex pátek 22. listopadu 2019 19:27
    pátek 22. listopadu 2019 19:26
  • So is SQL Server important on this system, or should it cooperate? If SQL Server is important, think hard about how much memory it should have, and tough luck to any other process, then set "min server memory (MB)" appropriate. You could also be more draconian and use the Lock Pages in Memory , but you need to be absolutely sure this is what you intend to do this
    I have the impression that MyCatAlex runs SQL Server on his/her laptop, so in that case I don't think setting min server memory is a good idea. However, setting "max server memory" is a good idea, so that SQL Server does not attempt to grab all memory that is available in the machine, only to be swapped when Alex starts something else that needs memory. I usually set max server memory to 4000 (which is in MB) on my workstations, although it depends on how much memory the machine has in total.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    pátek 22. listopadu 2019 23:05