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.
- 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.
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
- in progress - 0 pending grants (resource_semaphore waits)
- per second
- avg wait time
- SQL Server waits, also disk IO
- active requests
- active px workers
- page lookups/s
- page reads/s or page writes/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.