Short one for today, trying to get back into the habit of posting stuff here ;-)
I remember staring at all of the SQL Server memory-related counters available in perfmon, and wondering how I was going to make sense of all of them.
I remember the relief when I settled on "total = database + stolen + free", which aligned with the formula I usually used when evaluating UNIX server memory: total = file + computational + free.
Here's a link to a post from a few years ago about #SQLServer memory accounting...
Perfmon: SQL Server Database pages + Stolen pages + Free pages = Total pages
And here's a post going through a tricky detail when accounting for memory in AIX...
IBMPower AIX Memory Accounting: Fun with vmstat... Numperm Surprise!!
Its time to return to squaring the numbers between the DMVs and Perfmon. I want to make sense of resource_semaphore_query_compile waits; I'll have to evaluate threshold values from sys.dm_exec_query_optimizer_memory_gateways in light of optimizer memory, granted memory, used granted memory, and total stolen memory. The gateway threshold values aren't available in perfmon so I'd like to grab all of the numbers from the DMVs. We'll see.
Here's a query I'm using to square up the basics between perfmon and the DMVs.
The results are pretty close If anyone knows where the variance in database cache/stolen memory comes from (or how to get rid of it), please let me know. I'd really like to get it dead on, even though production system values will typically be changing over time even when measured in small increments.
Here's what I got on my test system as it was ramping up...