March 31, 2011 at 3:09 pm
[font="Courier New"]I've been having issues with one instance where it reports multiple occurrences of:
"There is insufficient system memory to run this query."
This is a 2005 x64 SP3 instance with a max memory setting of 8GB. At the time of the issues the CACHESTORE_SQLCP is consuming most of the memory allotted to the instance. This has happened several times over the last few months.
Below is a view of the cache from the SQL log during the problem:
CACHESTORE_SQLCP (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 7410624 KB
MultiPage Allocator = 0 KB
What is baffling me is that while the plan cache is consuming a huge amount of memory, there is a very small number of plans stored in it, and none of them are particularly large. While I was watching over the course of the day, the total of the cached plans never went above 5MB and has around 500 cached plans, but the CACHESTORE_SQLCP size increased from 5531248KB to 5704496KB. It has been growing steadily. Over the last week it has gone from 2711720KB to the current value.
Does anyone have an idea what might be causing this?
CACHESTORE_SQLCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 5704496
MultiPage Allocator 0
SELECT objtype AS [CacheType]
, count_big(*) AS [Total Plans]
, sum(cast(size_in_bytes as decimal(12,2)))/1024/1024 AS [Total MBs]
, avg(usecounts) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 AS [Total MBs - USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC
CacheType Total Plans Total MBs Avg Use Count Total MBs - USE Count 1 Total Plans - USE Count 1
-------------------- -------------------- --------------------------------------- ------------- --------------------------------------- -------------------------
Adhoc 497 3.968750 6 3.953125 495
Proc 1 0.125000 1 0.125000 1
View 1 0.023437 2 0.000000 0
Prepared 2 0.476562 935 0.000000 0
[/font]
April 5, 2011 at 9:47 am
How much RAM does the box have and how much is allocated to SQL Server?
If you don't set max ram in SQL Server (64bit) it will use almost all of it.
April 5, 2011 at 10:23 am
The server has 48GB of RAM total and 4 instances of SQL 2005 SP3. The max memory setting is set on each of the instances, and 16GB is left for the OS and other processes.
The instance I'm having trouble with has the max memory set to 8GB. On that instance the CACHESTORE_SQLCP buffer is using more of that 8GB than it should be able to, and at times it uses almost all of it and is causing out of memory errors on queries.
From 2005 SP2 onward the plan cache should be limited to "75% of visible target memory from 0-4GB + 10% of visible target memory from 4-64GB + 5% of visible target memory > 64GB" Plan Cache Internals
For an instance with a max memory setting of 8GB, this should be (75% * 4GB) + (10% 4GB) = 3.4GB. Once the server reaches 75% of this limit, it should start purging the oldest plans to free space. This behavior is what I'm seeing on my other instance on this server, which also have 8GB max memory, they float around 2.6GB in the plan cache.
On the problem instance, I've tried clearing out the buffer with DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS but the size of the CACHESTORE_SQLCP buffer doesn't decrease. That one buffer is currently sitting at 6.9GB of the 8GB max memory for the instance, which is over double what it should be limited to.
April 5, 2011 at 2:21 pm
Do you have CLR enabled across all 4 instances?
Can you run this query to check which query is taking up the most ram in cache
SELECT TOP(10) * FROM sys.dm_Exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND objtype = 'Adhoc' AND usecounts = 1
AND size_in_bytes < 5242880 ORDER BY size_in_bytes DESC
size_in_bytes is set to 2MB so you may also see systems queries.
April 5, 2011 at 3:06 pm
Thanks for the replies. CLR is not enabled on any of the instances on this server, and the results from the query are below.
What I'm finding odd is that the sum of all the sizes from the exec_cached_plans isn't anywhere close to what DBCC MEMORYSTATUS is reporting for CACHESTORE_SQLCP.
SELECT TOP(10) * FROM sys.dm_Exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND objtype = 'Adhoc' AND usecounts = 1
AND size_in_bytes < 5242880
ORDER BY size_in_bytes DESC
bucketid refcounts usecounts size_in_bytes memory_object_address cacheobjtype objtype plan_handle dbid objectid number encrypted text
----------- ----------- ----------- ------------- --------------------- -------------------------------------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------- ------ ----------- ------ --------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3894 2 1 73728 0x0000000436448170 Compiled Plan Adhoc 0x06000100B5052F1B40834436040000000000000000000000 NULL NULL NULL 0 SELECT TOP(10) * FROM sys.dm_Exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND objtype = 'Adhoc' AND usecounts = 1
AND size_in_bytes < 5242880
ORDER BY size_in_bytes DESC
10326 2 1 49152 0x000000043D9AA170 Compiled Plan Adhoc 0x06000A00741F7E0C40A39A3D040000000000000000000000 NULL NULL NULL 0 SELECT MAX(last_upd_date) FROM alerts
24774 2 1 16384 0x000000043AC8A170 Compiled Plan Adhoc 0x060009005DA6322740A3C83A040000000000000000000000 NULL NULL NULL 0 SELECT T1.tzone_id,T1.descr FROM tzone T1 (NOLOCK) WHERE (T1.std_bias=(-360))
5967 2 1 8192 0x0000000433F3E170 Compiled Plan Adhoc 0x06001300DC6AC00C40E3F333040000000000000000000000 NULL NULL NULL 0 FETCH API_CURSOR00000000007B86C9
16847 2 1 8192 0x000000042FB64170 Compiled Plan Adhoc 0x06001300233EA22F4043B62F040000000000000000000000 NULL NULL NULL 0 FETCH API_CURSOR00000000007B84F2
35180 2 1 8192 0x000000043A334170 Compiled Plan Adhoc 0x060013005B5FB72C4043333A040000000000000000000000 NULL NULL NULL 0 FETCH API_CURSOR00000000007B83D5
1995 2 1 8192 0x0000000430F96170 Compiled Plan Adhoc 0x06001300EAE52B394063F930040000000000000000000000 NULL NULL NULL 0 FETCH API_CURSOR00000000007B82D0
34783 2 1 8192 0x0000000419CAE170 Compiled Plan Adhoc 0x0600130077B3812D40E3CA19040000000000000000000000 NULL NULL NULL 0 FETCH API_CURSOR00000000007B8204
37343 2 1 8192 0x0000000420A90170 Compiled Plan Adhoc 0x060013005C85B7134003A920040000000000000000000000 NULL NULL NULL 0 FETCH API_CURSOR00000000007B7F92
16855 2 1 8192 0x000000041769C170 Compiled Plan Adhoc 0x06001300B9D9F33240C36917040000000000000000000000 NULL NULL NULL 0 FETCH API_CURSOR00000000007B7F76
(10 row(s) affected)
SELECT
sum(size_in_bytes) / 1024.0 AS [Size in KB],
count(*) AS [Number of plans]
FROM sys.dm_Exec_cached_plans
Size in KB Number of plans
--------------------------------------- ---------------
960.000000 108
(1 row(s) affected)
DBCC MEMORYSTATUS
CACHESTORE_SQLCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 7373760
MultiPage Allocator 0
April 6, 2011 at 6:35 am
1) as an interim fix, does the cache clear out if you run dbcc freeproccache? If so, set up a job to run that every so often to avoid the problem
2) I think you have hit a bug. Perhaps this one: http://support.microsoft.com/kb/970939. There could be others. I would try that patch out. I would also get on the phone with Microsoft Support to see if they know of any other bugs like that one.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 6, 2011 at 12:54 pm
Grant
How much is your "min memory per query"? If it's at 1024 double it see what happens.
July 10, 2012 at 9:35 am
I think Kevin is right on target. I have an instance like that. The fix is in CU4 after SP3.
Jason
http://dbace.us
😛
March 11, 2015 at 9:38 pm
Check to see if you have any stored procedures that use Table Valued Parameters. TVPs are implemented as CLR assemblies via Microsoft.SqlServer.Types. The application could exhaust this memory area if it passes too many rows via the TVP facility.
March 12, 2015 at 12:24 pm
Please don't respond to 4-year old threads Steve. Thanks!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 29, 2015 at 4:15 am
Steve Cornwell-278514
I found your 4 year old post relevant, and not something I have seen elsewhere or covered by anyone else on this thread so thanks - keep posting!
September 14, 2015 at 11:59 am
TheSQLGuru (3/12/2015)
Please don't respond to 4-year old threads Steve. Thanks!
Why not? If someone has a solution to even a really old thread, why not post it? It's not just the OP that's going to find this post in a Google search.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 17, 2018 at 1:16 pm
I know this is 4 year old thread but after searching for hours this is the only thread i found matching exactly with what we are seeing on SQL server 2016. Below are the details
Vesrion - Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
Total Memory - 128
Max Memory - 110
We are seeing Query plans getting cleared from sys.dm_exec_cached_plans within seconds. Upon further investigating we found even though there is almost nothing in sys.dm_exec_cached_plans DMV CACHESTORE_SQLCP has more than 18 GB allocated. We tried to run DBCC FreeProcCache but that didn't changed anything.
Thanks
Prashant
October 20, 2018 at 7:52 pm
2016 SP1 is not a place to be. I would recommend you patch up. Same thing that was the solution for this thread.
I would also recommend you get some professional help if this is a production server if that doesn't work (or if you cannot safely patch up). You have some wierd stuff going on.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply