I've done some further investigation into this issue and could still do with some help understanding what's going on.
After restarting the instance, I monitored memory allocations over time, and sure enough the SQL Cache Memory sqlos counter (CACHESTORE_SQLCP) grows steadily over time, until eventually there isn't enough workspace memory for new queries to execute.
I also monitored the contents of the plan cache over time, expecting this issue to be caused by a massive amount of cached ad-hoc query plans, however, this ultimately becomes almost completely truncated.
Currently the SQL Cache 'CACHESTORE_SQLCP' is 5.6GB while sys.dm_exec_cached_plans indicates that there are only 4 cached plans totaling only 3.5MB. I don't understand what's taking up all the space in the SQL Cache Memory, and why it doesn't release it's memory to allow queries to run. It seems it's already released all of the memory from Cache plans, and yet the memory object it's self stays at over 5GB.
I tried replicating this error in my lab by creating a database containing a few million rows, and using a PowerShell script to generate a few million ad-hoc queries. In this scenario, I was able to watch the cached plans for ad-hoc queries grow in number to around 3000 and then completely drop to 0. They then continued to grow to around 3000 before dropping back to 0. Meanwhile, the CACHESTORE_SQLCP remained very small.
I found someone had a similar issue due to a bug here: http://www.sqlservercentral.com/Forums/Topic1087140-146-1.aspx But this was on SQL2005, and the bug was fixed.
What am I missing? is the CACHESTORE_SQLCP made up of more than just the plan cache?
I am seeing some odd behaviour on one of my servers. It’s non production server, and is only used for my own testing and data collection, so I wanted to use this as a learning experience. I could do with some help understanding what’s going on.
SQL Server 2016 RC0 running on Windows Server 2012 in a VCenter Virtual Machine. 4 cores and 8GB Ram. Max Memory is configured as 6GB. MaxDop 4 and Cost Threshold For Parallelism is 40.
This is not a busy server. It only really hosts my DBA database and has a bunch of Agent jobs that collect various metrics and information from the estate. Most of these run at night, apart from some perf counters collected in the day.
I view the data in PowerBi. This morning I loaded up PowerBi and after hitting refresh, it took considerable time to run the queries and load the data from my DBA database. I found that everything was waiting on the RESOURCE_SEMAPHORE (so basically waiting on memory to be allocated for the query).
The PowerBi Queries did eventually complete, however, there is also a DBCC table check that started at midnight and is still waiting on memory (RESOURCE_SEMAPHORE). Nothing else is particularly going on in the server.
I found the session in sys.dm_exec_query_memory_grants, which showed requested_memory_kb as 1169128, required_memory_kb as 2408. Sys.dm_exec_query_resource_semaphores shows target, total and available_memory_kb to be 1059744. Notably less than the requested memory for the dbcc check.
sys.dm_os_memory_clerks indicates that the SQL Plans Cache is 3755616 and the SQL Buffer Pool is 2022864 (which is most of the available memory) Regarding Parallelism, sys.dm_exec_requests states that the dbcc check had a parallelism of 1, while sys.dm_exec_query_memory_grants shows the session having a dop of 4. Not sure why these are different.
I don’t understand what is causing the memory pressure. 6GB Max Mem isn’t a lot, but for what I’m using it for, it should be (and has been) fine.
If the dbcc query is actually only stating it ‘requires’ 2 MB to run why can’t it continue. Will it just wait until available_memory_kb can meet its requested_memory_kb? I’m not sure why it can’t just spill to disk at the very least. Shouldn’t SQL release some memory from the buffer pool, or plan cache in order to fulfill queries that are coming in? PLE has of course dropped down, indicating this is happening.