What does 0 memory grants pending, but low PLE, high lazy writes/sec and free list stalls/sec indicate?



  • My server memory is 100GB, of which 90GB is allocated to SQL server via the max memory setting.

    I am monitoring a sql server that has got 0 memory grants pending. This means no process is waiting for memory. If this was greater than 0 then it will indicate memory pressure which would get resolved upon increasing the max sql server memory setting.

    However I am seeing low PLE (page life expectancy), high lazy writes/sec and free list stalls/sec. Does this indicate memory pressure?



  • Memory grants is just one usage of memory (a grant for memory, for instance for a sort operation). It does not include "regular" memory usage, like caching data from your database files.

    It seems like you read and modify lots of data, to the extent that SQL Server can't keep the "active portion" of your databases in memory. This means that SQL server will throw things out of cache very frequently in order to have place for the data your queries refer to (for your SELECTs and modification).

    The fact that you have high high lazy writes/sec and free list stalls/sec suggests that queries needing data to be put in memory cannot be satisfied without removing other data in memory including data that was modified since it was brought into memory. In general, CHECKPOINT takes care of that, but you seem to have a so high turnover of your data compared to memory size so checkpoint can't cope with that.

    to me, this seems like a classic example of "add more memory or tune your queries" situation.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2