SQL CLR taking up all the VAS memory area ! Any solution

Question

Post reply

SQL CLR taking up all the VAS memory area ! Any solution

Dean Jones-454305

SSCarpal Tunnel

Points: 4804
More actions
November 11, 2008 at 4:16 am

#127571

Hi,
I have a bit of a problem with one of my systems, it constantly uses CLR for finding out file details, but we seem to be running low on VAS memory area, I can see a lot of memory pressure errors on the SQL error logs. I increased the –G parameter settings to 512, and after a restart the figure came up, but now its down again to 13mb.
It’s the CLR which is taking up all the memory space, but this issue is so controversial that there doesn’t seem to be a solution out there, does anyone have some bit of clarity on this matter.
Server details:
X86 System (32 bit)
AWE not enabled.
Physical RAM: 16GB
SQL 2005 default instance.
Min server memory: 0
Max server memory: 10240
Server is a dedicated SQL box.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

winash SSChampion Points: 11446 More actions · Answer 1

What kind of memory pressure errors are you seeing in the SQL error log? Are you using CLR TVFs, SPs or UDTs?

Have you applied SQL Server SP2? (there were CLR memory issues prior to SP2)

We had memory issues with CLR TVFs that went away after applying Cumulative Update 8 (http://support.microsoft.com/kb/953497/LN/) (this applies if you are using CLR TVFs and are seeing the "AppDomain unloaded due to memory pressure" errors in your error log)

Dean Jones-454305 SSCarpal Tunnel Points: 4804 More actions · Answer 2

winash (11/11/2008)
What kind of memory pressure errors are you seeing in the SQL error log? Are you using CLR TVFs, SPs or UDTs?
Have you applied SQL Server SP2? (there were CLR memory issues prior to SP2)
We had memory issues with CLR TVFs that went away after applying Cumulative Update 8 (http://support.microsoft.com/kb/953497/LN/) (this applies if you are using CLR TVFs and are seeing the "AppDomain unloaded due to memory pressure" errors in your error log)

Thanks for the info, we have SP2 + SQL Cumulaive update 6, I have seen the link you sent me before and when I tried applying it, it said my current setup is more up to date than the patch I was trying to apply.

Do you have AWE enabled and how have you configured this ?

I ran

SELECT

SUM( multi_pages_kb +

virtual_memory_committed_kb +

shared_memory_committed_kb +

awe_allocated_kb ) AS [Used by BPool, KB]

FROM

sys.dm_os_memory_clerks

WHERE

[type] = 'MEMORYCLERK_SQLBUFFERPOOL'

The figure I am ge ting back is pointing towards 1.6GB or so, does this mean that I am not fully utilising all the memory in the system jsut becasue I do not have AWE enabled ?

sgmunson SSC Guru Points: 110640 More actions · Answer 3

The ability to use more RAM has more to do with the operating system, as 32-bit versions of Windows Server products cannot allocate more than 2 GB to any one process, unless the /3GB switch is turned on in BOOT.INI, and then the max is 3GB. With 16GB of RAM, but only a 32-bit operating system, you've got serious overcapacity that's never going to see much use. To my knowledge, the best way to solve that problem is to install the 64-BIT version of the operating system, along with a 64-bit version of SQL Server.

However, those are major tasks that would require pretty significant effort, along with likely license cost implications. Until you can get to that point, there are two things that I know relate to using more than 2GB of RAM in a process beyond the /3GB switch: AWE and PAE. I'm not sure exactly where you turn on AWE, nor am I sure what it does, exactly. There's also PAE, and I'm pretty sure you need at least one of these two turned on. There's bound to be someone on the forum that has the details on those and can point you in the right direction on their use. Google is also likely to be capable of finding plenty of detail on them.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

if you have 4GB RAM or over you need to enable AWE for each instance. What version OS do you use and what version SQL server. You should also configure the min and max memory settings to be your ideal figures.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Dean Jones-454305 SSCarpal Tunnel Points: 4804 More actions · Answer 5

Perry Whittle (11/12/2008)
if you have 4GB RAM or over you need to enable AWE for each instance. What version OS do you use and what version SQL server. You should also configure the min and max memory settings to be your ideal figures.

Hi,

yes, we do have over 4GB of memory on the system, windows 2003 server Enterprise & SQL 2005 Standard.

My experience in the past is that AWE does have its problems when enabled and doenst really solve the issue of SQL CLR taking up the whole VAS memory area. Any ideas on this ?

sgmunson SSC Guru Points: 110640 More actions · Answer 6

As CLR should in most cases just not be necessary, one way to help with it's memory usage would be to ensure it's only used for scalar functionality, which means it shouldn't be doing any database interaction independent of it's being called by the database to do something it's good at. Having a CLR function independently seek out database data is not a terribly good scenario to be in when you have memory constraints, and is quite likely not a terribly good idea in the 1st place.

Steve

(aka smunson)

:):):)

Dean Jones (11/12/2008)
Perry Whittle (11/12/2008)
if you have 4GB RAM or over you need to enable AWE for each instance. What version OS do you use and what version SQL server. You should also configure the min and max memory settings to be your ideal figures.
Hi,
yes, we do have over 4GB of memory on the system, windows 2003 server Enterprise & SQL 2005 Standard.
My experience in the past is that AWE does have its problems when enabled and doenst really solve the issue of SQL CLR taking up the whole VAS memory area. Any ideas on this ?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

Dean Jones (11/12/2008)
when enabled and doenst really solve the issue of SQL CLR taking up the whole VAS memory area.

that's not what AWE is for. AWE is so that each SQLSRV.EXE process spawned can address memory above the 4GB limit. Switch it on and configure your min and max memory settings. If you dont configure it correctly you can get problems

< 4Gb RAM use the /3GB switch

4GB > 8GB use AWE and /3GB switch

12GB or more use AWE only

you must configure min and max memory

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Dean Jones-454305 SSCarpal Tunnel Points: 4804 More actions · Answer 8

smunson (11/12/2008)
As CLR should in most cases just not be necessary, one way to help with it's memory usage would be to ensure it's only used for scalar functionality, which means it shouldn't be doing any database interaction independent of it's being called by the database to do something it's good at. Having a CLR function independently seek out database data is not a terribly good scenario to be in when you have memory constraints, and is quite likely not a terribly good idea in the 1st place.
Steve
(aka smunson)
:):):)
Dean Jones (11/12/2008)
Perry Whittle (11/12/2008)
if you have 4GB RAM or over you need to enable AWE for each instance. What version OS do you use and what version SQL server. You should also configure the min and max memory settings to be your ideal figures.
Hi,
yes, we do have over 4GB of memory on the system, windows 2003 server Enterprise & SQL 2005 Standard.
My experience in the past is that AWE does have its problems when enabled and doenst really solve the issue of SQL CLR taking up the whole VAS memory area. Any ideas on this ?

Hi

The reason why the CLR was used in the form of a procedure is to duplicate xp_getfiledetails on SQL 2005. This doesnt ship with 2005 by default and the functionality is needed. Its just that one of its side effects is that of memory pressure problems.

sgmunson SSC Guru Points: 110640 More actions · Answer 9

If you're seeking file details, such as filename, file size, etc., why not use xp_cmdshell and run a VBScript that can get that information? It might have less overhead than CLR, but no way to know without testing. I wouldn't expect to see anywhere near as much, but one never knows for sure...

Steve

(aka smunson)

:):):)

Dean Jones (12/5/2008)
smunson (11/12/2008)
As CLR should in most cases just not be necessary, one way to help with it's memory usage would be to ensure it's only used for scalar functionality, which means it shouldn't be doing any database interaction independent of it's being called by the database to do something it's good at. Having a CLR function independently seek out database data is not a terribly good scenario to be in when you have memory constraints, and is quite likely not a terribly good idea in the 1st place.
Steve
(aka smunson)
:):):)
Dean Jones (11/12/2008)
Perry Whittle (11/12/2008)
if you have 4GB RAM or over you need to enable AWE for each instance. What version OS do you use and what version SQL server. You should also configure the min and max memory settings to be your ideal figures.
Hi,
yes, we do have over 4GB of memory on the system, windows 2003 server Enterprise & SQL 2005 Standard.
My experience in the past is that AWE does have its problems when enabled and doenst really solve the issue of SQL CLR taking up the whole VAS memory area. Any ideas on this ?
Hi
The reason why the CLR was used in the form of a procedure is to duplicate xp_getfiledetails on SQL 2005. This doesnt ship with 2005 by default and the functionality is needed. Its just that one of its side effects is that of memory pressure problems.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

smunson (12/5/2008)
If you're seeking file details, such as filename, file size, etc., why not use xp_cmdshell and run a VBScript that can get that information? It might have less overhead than CLR, but no way to know without testing. I wouldn't expect to see anywhere near as much, but one never knows for sure...
Steve
(aka smunson)
:):):)

Why would you recommend opening pandora's box (xp_cmdshell) when CLR is Microsoft's own recommended way to do the task that Dean is doing? You provide carte blanche access to the OS by enabling xp_cmdshell. CLR at least restricts the operation being performed to the abilities of the function or stored procedure. From a security standpoint your recommendation above is horrible, and CLR is absolutely more secure.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 11

Dean Jones (11/11/2008)
I ran
SELECT
SUM( multi_pages_kb +
virtual_memory_committed_kb +
shared_memory_committed_kb +
awe_allocated_kb ) AS [Used by BPool, KB]
FROM
sys.dm_os_memory_clerks
WHERE
[type] = 'MEMORYCLERK_SQLBUFFERPOOL'
The figure I am ge ting back is pointing towards 1.6GB or so, does this mean that I am not fully utilising all the memory in the system jsut becasue I do not have AWE enabled ?

Dean as someone else stated AWE and VAS have nothing to do with each other. What is the output of the following query?

select single_pages_kb + multi_pages_kb + virtual_memory_committed_kb

from sys.dm_os_memory_clerks

where type = 'MEMORYCLERK_SQLCLR'

Then what is the output from this query?

With VASummary(Size,Reserved,Free) AS

(SELECT

Size = VaDump.Size,

Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)

WHEN 0 THEN 0 ELSE 1 END),

Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)

WHEN 0 THEN 1 ELSE 0 END)

FROM

(

SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes))

AS Size, region_allocation_base_address AS 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), region_allocation_base_address

FROM sys.dm_os_virtual_address_dump

WHERE region_allocation_base_address = 0x0

)

AS VaDump

GROUP BY Size)

SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [Total avail mem, KB] ,

CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB]

FROM VASummary

WHERE Free <> 0

Then can you post the specific errors that you are getting in your errorlog onto this thread? If you are getting OOM (out of memory) exceptions while running code, what are the messages from those?

sgmunson SSC Guru Points: 110640 More actions · Answer 12

Only to get past the immediate crisis... It appears that this is yet another scenario where it's likely that more hardware is essential to the future of the databases involved, but it's unlikely anyone is prepared for those costs. There may simply be no other "affordable" alternative.

Obviously, I'm not looking to open a gaping hole, but I don't think we have enough information about the original poster's environment to determine the overall risk of using xp_cmdshell. That, to me, is an inherent responsibility of the original poster.

Steve

(aka smunson)

:):):)

Jonathan Kehayias (12/5/2008)
smunson (12/5/2008)
If you're seeking file details, such as filename, file size, etc., why not use xp_cmdshell and run a VBScript that can get that information? It might have less overhead than CLR, but no way to know without testing. I wouldn't expect to see anywhere near as much, but one never knows for sure...
Steve
(aka smunson)
:):):)
Why would you recommend opening pandora's box (xp_cmdshell) when CLR is Microsoft's own recommended way to do the task that Dean is doing? You provide carte blanche access to the OS by enabling xp_cmdshell. CLR at least restricts the operation being performed to the abilities of the function or stored procedure. From a security standpoint your recommendation above is horrible, and CLR is absolutely more secure.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 13

There is no information in this post as of yet that actually attributes a problem to CLR other than the statement by Dean that CLR was causing the problem. Empirical data is needed to determine if CLR is really the problem, or if VAS fragmentation is really the problem. They are distinctly different issues, and the answers to them take different paths.

There is personal responsibility in that the poster should know what the implications are for making your changes, and then there is responsible posting when trying to help someone as well. Recommending xp_cmdshell be enabled without explaining the dangers behind doing so is irresponsible posting. It may not be that Dean doesn't understand this, but forums are highly searchable, and a new developer could read your post on this thread and follow an unsafe process unknowingly. Then because it happened to work, that person moves on recommending the same, without warning to others in the community. Don't think it happens, consider why SQLInjection is such a large problem. The number of examples 2 years ago of inline sql and value concatenation versus parameterization was probably 10 to 1. That is changing now that everyone has been eaten alive by it.