September 2, 2009 at 8:40 am
I am running an application that calls code in a C# assembly. Over time, the committed CLR memory approaches the reserved CLR memory amount, and the application fails with error:
Error: 701 Severity: 17 State: 13
There is insufficient system memory to run this query.
I am running 32 bit servers, (64 bit servers are not an option currently), SQL Server 2005 SP2
One solution is to allocate more memory at server startup to MemToLeave. But this only gives me more time until the same failure happens. Will SQL Server 2005 SP3 help sql server to release committed CLR memory? Or can I force SQL Server to release this committed memory without ?
In addition, my understanding of CLR memory is that it should get 256MB reserved by default, but when I start my server, I get only 100MB...What configuration should I change to enable the MEMTOLEAVE to get at least 256 MB of memory? Thanks in advance for any help I can get!
September 8, 2009 at 12:29 pm
in which situation you received this error ?
September 8, 2009 at 1:31 pm
I received this error when trying to execute a query that called a function that then called an assembly. The assembly contains numerous pieces of Code written in C#. I cannot view the C# code...The only solution was to stop and start the database service. The query then ran fine...I am looking for a way to relieve the pressure and allow the code to run without bringing the database server down.
September 8, 2009 at 1:37 pm
I can't help on the problem you're having, Daniel, but I am curious... what does the CLR do?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 8, 2009 at 4:20 pm
If you really must run this code sight-unseen, you can configure a larger multi-page allocator region using the -g start-up switch:
http://msdn.microsoft.com/en-us/library/ms190737.aspx
On the face of it, it seems a terrible idea - especially in the 32-bit version where Virtual Address Space (VAS) is at a premium. Be very very careful with this.
If you are able to give more details, please do.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 9, 2009 at 7:23 am
I have had the same problem in the past (on 32-bit environment).
I used the -g startup parameter (I specified it as -g384), and the problem went away.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 9, 2009 at 7:47 am
Marios Philippopoulos (9/9/2009)
I have had the same problem in the past (on 32-bit environment).I used the -g startup parameter (I specified it as -g384), and the problem went away.
For a while at least 🙂
VAS fragmentation is a pernicious problem on 32-bit.
The 384MB of VAS you are allocating to the multi-page allocator there (plus the 128MB stack space allocation) directly subtract from the VAS available for buffer pool - unless AWE is enabled. 64-bit is the only permanent solution.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 9, 2009 at 8:11 am
Paul White (9/9/2009)
Marios Philippopoulos (9/9/2009)
I have had the same problem in the past (on 32-bit environment).I used the -g startup parameter (I specified it as -g384), and the problem went away.
For a while at least 🙂
VAS fragmentation is a pernicious problem on 32-bit.
The 384MB of VAS you are allocating to the multi-page allocator there (plus the 128MB stack space allocation) directly subtract from the VAS available for buffer pool - unless AWE is enabled. 64-bit is the only permanent solution.
True, I forgot to mention that I had AWE enabled with quite a bit more memory allocated than what is normally available on 32-bit access space. Without AWE enabled, adding the -g startup flag is probably not advisable.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 9, 2009 at 2:50 pm
Sounds like a caching problem. CLR functions are implemented as "static" Method. If they use caches within those modules those caches will not be freed until SQL Server unloads the App-Doamin.
Some scenarios which can cause memory problems due to static cahces:
* Caching of compiled XSLT transformations to to optimize execution
* Caching of compiled regular expressions to optimize search/replace functionalitiy
* Incorrect cache handling for aggregate functions
* Caching of master data
Ask your developers if they use any kind of static caching. If yes tell then to use a ring-buffer instead of a usual List or Dictionary.
Greets
Flo
September 11, 2009 at 6:28 am
Thanks all for the responses. I am aware of the -g switch at startup. As far as the app developers, this is an off the shelf app where I have no input to the development of the various parts of the app. I get what I get... My best recourse is of course to move to 64 bit servers. I am testing the increased memtoleave space currently. An alternate is to restart the db engine very 3 weeks or so...(not the best solution).
Again, thanks for the discussion,
Daniel
September 11, 2009 at 6:51 am
Last thing (can't believe I didn't mention this before):
If it is VAS pressure/fragmentation (it almost always is, especially if you can last for 3 weeks before a reboot), use the VASummary view in this awesome article by Slava Oks.
I used to use it on our old 32-bit servers - I recorded the results every five minutes and knocked up a very quick SSRS report on the log table.
This is a great way to show total VAS space and the largest available contiguous VAS fragment over time. The shape of the graph can help you isolate the cause, or at least predict when the next reboot would be required.
In one case, it turned out to be the CLR being loaded (though disabled!) because we were calling sp_browsereplcmds which calls a system CLR function. The 100MB VAS required by CLR killed a production server more than once...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 11, 2009 at 9:20 am
Paul's last post stirred up my memory too! 🙂
Here is some code (probably similar to the one Paul just posted) that returns all VAS fragments (sorted by size, at bottom are the largest ones).
Taken from http://social.msdn.microsoft.com/forums/en-US/sqlnetfx/thread/cc1b3e43-0db8-4e75-b5ab-bc2a4c93b12b/
-- from http://social.msdn.microsoft.com/forums/en-US/sqlnetfx/thread/cc1b3e43-0db8-4e75-b5ab-bc2a4c93b12b/
-- MemToLeave values for instance of SQL Server:
-- [Total avail mem, KB] : total memory assigned to MemToLeave
-- [Max free size, KB] : free memory available to MemToLeave
;WITH VAS_Summary AS
(
SELECT
[Size] = VAS_Dump.Size,
Reserved = SUM(CASE(CONVERT(INT, VAS_Dump.Base)^0) WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VAS_Dump.Base)^0) WHEN 0 THEN 1 ELSE 0 END)
FROM
(
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes)) [Size],
region_allocation_base_address [Base]
FROM
sys.dm_os_virtual_address_dump
WHERE
region_allocation_base_address 0x0
GROUP BY
region_allocation_base_address
UNION
SELECT
CONVERT(VARBINARY, region_size_in_bytes) [Size],
region_allocation_base_address [Base]
FROM
sys.dm_os_virtual_address_dump
WHERE
region_allocation_base_address = 0x0
) AS VAS_Dump
GROUP BY [Size]
)
--Get size of all free mem. areas
--http://www.sqlservercentral.com/Forums/Topic624152-386-2.aspx?Update=1
--
SELECT
CAST(Size AS BIGINT)/1024 AS [Free size, KB]
FROM
VAS_Summary
WHERE
Free 0;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 11, 2009 at 9:25 am
Yep. Same code. It's very popular 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 11, 2009 at 10:37 am
I agree. I have been running that code. The issue just happened again. During this issue the results of the above query was
Total avail mem, KB Max free size, KB
51716 8704
We were forced to restart our sql server service. After the restart the values were:
Total avail mem, KB Max free size, KB
154256 45632
We are now functional again. It took 11 days for the error to reappear. This is a type of memory fragmentation , yes?
Also during this issue I was not able to run a simple function that calls the C# assembly. It errored with the following error:
Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65537. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileNotFoundException: Could not load file or assembly 'colleague, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.
System.IO.FileNotFoundException:
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
It seems that 8074KB should be plenty of memory to run a simple function...
September 11, 2009 at 10:48 am
danielmanke (9/11/2009)
It seems that 8074KB should be plenty of memory to run a simple function...
How many times is this function being executed though?
That would contribute to the mem utilization you are seeing.
Also, are you seeing messages in your ERRORLOG to the effect of "Appdomain being unloaded"?
Here is an interesting link:
http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/e5ca2988-df87-4ce4-8fb7-b338a81a390e
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply