My research into the Resource Governor question and its answer brought up another question for me:
SELECT * FROM sys.dm_exec_query_resource_semaphores
Where does that total_memory_kb number for the default pool actually come from? After all, I have a max server memory of 26000MB for the instance, so why 18175MB? The BOL description for this field wasn’t very helpful. Googling for total_memory_kb didn’t really yield me much. Most of what I found was unrelated to this particular DMV.
So I started looking at the sys.dm_os_performance_counters, and there I finally found something that looked promising.
select cntr_value FROM sys.dm_os_performance_counters WHERE object_name like '%Memory Manager%' and counter_name = 'Maximum Workspace Memory (KB)'
If you add up the 2 total_memory_kb values for the default pool, you get the maximum workspace memory. Woot!
That still didn’t tell me how that particular value was derived, but it gave me something better to search on. And that search returned this article at SQLMag.com which says (emphasis mine):
The SQL Server: Memory Manager object includes the following workspace-related PerfMon counters: Memory Grants Pending, which tells you the number of waiting queries; Memory Grants Outstanding, which counts the number of queries that have grants and are currently executing; Granted Workspace Memory (KB), which measures the total amount of grants SQL Server has made to running queries; and Maximum Workspace Memory (KB), which measures the maximum amount of memory SQL Server is currently willing to devote to workspace-memory grants. In general, SQL Server is able to devote between 25 and 75 percent of the buffer pool’s virtual memory to workspace memory. The value varies based on the demand for memory for other uses (such as database page cache and plan cache). If demand is low, SQL Server devotes a larger fraction of memory to workspace memory needs; if demand is high, SQL Server devotes a smaller fraction to workspace memory.
So there you have it. I feel better, don’t you feel better?
That entire article is worth reading for a better understanding of SQL Server memory. In fact, it helped me put together a bit of code to show me how memory is being used by SQL Server. More on that in a future post (I’ve got to give you a reason to keep coming back, right?).