April 10, 2008 at 11:34 pm
I'm running 32-buit SE. AWE Enabled and plenty of RAM on the box.
A CLR Stored proc started to fail after running OK since release 10 days ago. The proc does some CPU-intensive XML operations. Error:
Executed as user: myserver\myProxy. ...cute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 01:30:00 Error: 2008-04-10 01:34:56.20 Code: 0xC002F210 Source: Stored Proc to Execute SQL Task Description: Executing the query "EXEC dbo.myStoredProc ?" failed with the following error: ".NET Framework execution was aborted by escalation policy because of out of memory. System.Threading.ThreadAbortException: Thread was being aborted. System.Threading.ThreadAbortException: at xxxx.yyyyyy.zzzzzzzzz(String v_strName, SqlXml v_objXml, String v_strXsl, Boolean v_bln) ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 01:30:00 Finished: 01:34:56 Elapse. The step failed.
This is failing from a SSIS job. If I run the stored proc from SSMS I get similar errors. The -g option seems to cover part of this problem, but I have not seen the specified errors in my SQL logs ("Failed Virtual Allocate Bytes...") - this is described in "Using the SQL Server Startup Options".
I was able to re-create (on a test server) by re-starting with -g100.
I'm under pressure to get this working. I have just re-started the server and the job ran cleanly. However, will this cure the problem after 10 days ?
My questions:
1 - has anyone seen this (ie "escalation policy because of out of memory")?
2 - has anyone used the -g option at atartup? I'm especially concerned about the impact on other applications on the server.
3 - is there a possible memory leak and - if so - how can I identify it?
April 11, 2008 at 12:07 am
is your CLR proc using any IDisposables without disposing them?
---------------------------------------
elsasoft.org
April 11, 2008 at 12:35 am
Hi
Thanks for the reply.
I've asked the programmers to check, but they are confident that the CLR is not leaving anything around.
(I don't have access to the code!)
April 11, 2008 at 8:10 am
What version are you on (meaning - patch level)? CLR was more or less a large memory leak on anything prior to SP2.
Also - you seem to be using the -G parameter to LOWER the amount of memory available outside of the memory pool. I'm thinking that's a bad idea, you'd want to INCREASE the memory, not decrease it (this is where SQLCLR "plays" so restricting that will cause it to have trouble.) Without the -g flag, the server would reserve 256MB, so cutting that number to 100MB wouldn't be a great idea. I'd actually consider going to 512 or 768 if that doesn't start "pissing off" other processes.
Finally - depending on how big the XML files are, your XML handling syntax may be forcing the entire document to be materialized in memory. Things like the XPATHnodeiterator functions would do that for example, instead of streaming things in (and thus not needing the entire file in memory); in general, streaming things where you can would decrease your memory pressures.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 14, 2008 at 12:19 am
Hi thanks for the reply,
I'm running SP2.
I used the -G to reproduce the error - It's not set on the production system. I may have to use it, though. Not sure about the impact on the other applications so I'm holding back as long as I can.
I suspect that you are correct about the XML processing.
for the time being, I have run up a new SQL instance only for the application so that I can isolate the problem and - if needs be - start testing the -g flag.
April 15, 2008 at 5:49 am
The OP said there was plenty of memory on the box.
Remember that with 32-bit SQL Server, the ONLY thing that can use memory above the 4GB line is the buffer cache. Everything else, including CVR, must survive on memory below 4 GB, and you don't even get 4GB to play with!
If you have the /3GB switch set, then SQL, etc, has 3 GB memory, otherwise it only has 2 GB memory with the rest of the sub-4GB memory going to the OS.
Therefore, a 32-bit box can seem to have lots of memory, but still have severe memory pressure below the 4GB line. The best solution if you have this situation is move to 64-bit.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 15, 2008 at 6:25 am
Be happy it's only the clr going down.
I've had a whole instance going down when activating a memory hog CLR function.
http://www.sqlservercentral.com/Forums/FindPost457289.aspx
IMO This is one of the reasons to avoid CLR stuff in the current version of sqlserver.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 15, 2008 at 9:26 am
it is not easy to diagnose resource leaks in managed code. to do it in managed code used by sql server would be even harder. I've diagnosed plenty of leaks in managed apps, but never in CLR procs as I don't use them much, and the ones I do use I haven't had problems with.
That said, one way comes to mind. To find out which managed classes are using the memory, you could attach windbg to the sqlservr.exe process - wait until you the leak is apparent. Then use SOS.dll to find out who is to blame. Only attempt this if you already are well experienced with windbg and SOS though. If you don't know what windbg is, don't attempt this!
Also, never do this on a production server! Attaching windbg will bring everything to a screeching halt. your sql server will no longer be able to service any requests.
---------------------------------------
elsasoft.org
April 16, 2008 at 12:12 am
Thanks. I'll try to set up a test environment - if I get anywhere I'll post back.
Putting the application on its own SQL Server instance seems to have cleared the problem, so I suspect that this is linked to the size of other buffers or usage patterns.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply