1

Update

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?

Many thanks.

Sam


Introduction

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.

Scenario

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.

Question

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.

  • Are your Indexes and stats up-to-date ? Can you check the query plans for any improvement - missing index, etc ? How big is the database ? – Kin Shah Mar 18 '16 at 15:07
  • Hi There, thanks for response. Stats and indexes updated each weekend. DB is 2Gb + 2GB tlog. There are missing indexes etc, which will contribute to the Bi report being slow. It was suddenly much slower this morning however. The main thing that I'm unclear about, is why the DBCC check can't run (still waiting on resource_semaphore. Perhaps there simply isn't enough memory, which would be an easy fix. – Sam Partridge Mar 18 '16 at 15:17
  • I would doubt CHECKDB being slow for a 4GB database - its much faster in sql server 2016 RC0. Also, Big Gateway is on by default in SQL Server 2016 RC0. Check if your host that is hosting your virtual is not saturated ? Is your virtual machine configured properly ? – Kin Shah Mar 18 '16 at 15:24
  • I'm slightly blind to the VM config as the Server team provisions this. I did ask them to check, and apparently there's no visible latency or contention. Resources are also fat provisioned. The dbcc check is from ola's scripts and normally takes around 8mins to complete. Today, it hasn't even started however. – Sam Partridge Mar 18 '16 at 15:30
  • Can you try manually running the checkdb and see how long it takes to complete ? If you can, just reboot the VM to free up additional resources. – Kin Shah Mar 18 '16 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.