0

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?

2
  • What do the wait statistics say?
    – Sranda
    May 5, 2022 at 9:36
  • Specifically which wait thread counter are you referring to?
    – variable
    May 5, 2022 at 9:45

3 Answers 3

2

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.

1

As Tibor Karaszi mentioned memory grant is just one parameter to check for the underlying issue. I would start looking at SQL Server wait statistics first to derive any path for me to fix actual issue.

You may download FirstResponderKit and run below:

EXEC sp_BlitzFirst

Check the top wait statistics and see what they point towards, there is link for every wait type.

My guess would be(based on memory issue), you could have wait type PAGEIOLATCH_*

Your start point should be to find those queries or processes which are doing very heavy logical reads, try to fix them either by supporting index(es) - works in most of the cases. In case, you need to change query and you are permitted to do so - please do that, however test it thoroughly in Test environment before promoting them to Production. As a DBA, my first bet would to rely on index.

Depending on your wait type, you may take a call accordingly. Below link would help you to go in the right direction:

https://www.brentozar.com/sql/wait-stats/

You may also run query from below link to find wait statistics directly and act:

https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

Hope this helps.

1

Let's start with some discussion of modeling database behavior, performance and scalability. SQL Server waits get a lot of attention, but they are only part of the story.

In fact, in the context you mentioned above (low PLE & high lazy writes/s & high free list stalls/s), I think waits alone will have very little explanatory power.

What version and CU of SQL Server are you working with?

What factors on the system can help us build a behavioral picture? You included some of them.

System provisioning

  • 100 gb vRAM (i am assuming this is a vm)
  • SQL Server Max Server Memory: 90 GB
  • How many vcpus?
  • How many SQLOS memory nodes? SELECT count(*) FROM sys.dm_os_memory_nodes where memory_node_id < 64; -- if there are multiple SQLOS memory nodes a concern becomes whether each vNUMA node has the same number of vcpus and the same amount of vram.

Resource Utilization

  • rates(eg disk bytes/s, disk IOs/s), percentages (CPU busy), footprint (memory mb)

  • queue length - direct measure snapshot values are better than averages. "current disk queue length" counters are a good example

Waits

  • in progress - 0 pending grants (resource_semaphore waits)
  • per second
  • avg wait time
  • SQL Server waits, also disk IO

Work

  • active requests
  • active px workers
  • page lookups/s
  • batches/s
  • page reads/s or page writes/s
  • tx/s
  • rate of busy work (free list stalls/s, locks/s, spinlocks/s)

Here are reasons i don't think waits alone have much explanatory power for low PLE & high lazy writes/s & high free list stalls/s. Low PLE actually doesn't imply anything about waits. Low PLE implies something about resource utilization and rate of work. Page life expectancy is an estimate of the expected time a database page will remain in cache without being referenced again(and having its expected life in cache altered).

Assuming database cache remains the same size, what workload changes would lead to a lower PLE? Well, anything that would increase the rate of page eviction from cache would drive down PLE. That could be an increased rate of page reads into database cache. Could also be an increased rate of first-write-to-page. If a page is empty and being written to for the first time - no need to read it before it gets written into the database cache. With a fixed database cache size, increased read rate and increased first-write rate can both decrease PLE.

Another way for PLE to be driven down or be kept down is to shrink the size of the database cache. That can happen in a few ways. If [Target Server Memory] decreases due to low memory notifications, database cache may shrink in order for [Total Server Memory] to be brought down to the new lower [Target Server Memory]. If [Total Server Memory] remains the same but database cache shrinks (assuming the same workload pressure), eviction rate will increase and PLE will drop. What would cause database cache to shrink when [Total Server Memory] remains the same size?

Pressure from stolen memory. And pressure from free memory. Pressure from free memory is rare and i've only seen it in cases of intermittent column store use when there are rapid and durative increases in free memory. What could lead to pressure from stolen memory? The one i usually see is used workspace memory, stolen against granted memory. The fact that no sessions are pending memory grants doesn't tell me how many outstanding memory grants there are on the system doing work and using memory from their memory grants. In order to use sort/hash/columnstore compress workspace memory, it has t be stolen against the grant. Unless free memory on its own can absorb the allocations as sessions steal memory against their grants, database cache memory pages may be freed in order to then be stolen against grants. That freeing of database cache is one of the actions which may result in a shrinking of database cache size and driving down PLE. Lock memory is another memory type that I sometimes see result in a shrinking database cache. When I normally take note of lock memory is when lock escalation on a very large table fails for some reason, and the query I expected to escalate to a table lock instead accumulates hundreds of thousands of page locks.

Another possibility which can likely be ruled out quickly: automatically seeding an Availabilty Group Secondary - either over a high latency connection or with error occurrences on the secondary. Let's see. Is Hekaton in use in the instance? That introduces a new potentially significant memory consumer in SQL Server. Is SQL Server 2019 memory-optimized tempdb metadata enabled? This is also Hekaton and can be a significant memory consumer all by itself.

If the system has more than one SQLOS memory node, then questions of balance arise. The system-wide PLE is an harmonic mean of the PLE on each SQLOS memory node. If resources or work are unevenly divided among SQLOS memory nodes, one of the nodes may have a very low PLE (and high rate of free list stalls) and disproportionately affect the whole system.

2
  • I'm using 24 vcpu's which ofcourse leads to more waits as more parallelism is possible (max dop 8). My memory is 100GB, 90GB allocated to sql in the max setting. Then there is memory required for OS, other programs, ssis, and AG. Do you have any reference that helps to estimate how much memory the AG technology requires?
    – variable
    May 6, 2022 at 8:41
  • Not too concerned about the waits. Although waits can be associated with low PLE, high writes, or free lists stalls - no waits can directly cause them. cxpacket, cxconsumer waits especially unrelated. With the AG, the specific concern is with automatic seeding. Unless a recent change, issues with automatic seeding can cause MEMORYCLERK_SQLGENERAL to grow excessively large. If database cache drops to <= 3% of target on any SQLOS memory node, free list stalls take over.
    – sqL_handLe
    May 6, 2022 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.