SQL, SQLCLR objects & effective memory utilization

We've recently converted our ERP system from IBM Universe to SQL Server. Application performance is generally tolerable but occasionally degrades to terrible.

We're running the database on Win Server 2012 on VMWare with 32 Gb of RAM, and SQL Server 2012. SQL Max Memory is set at 27Gb. The db server is hosting this database only and isn't performing any other functions. Total db size is ~110Gb. The application has it's own dedicated server.

The vendor has used CLR's extensively to port the code (more than 36,000 Scalar Functions). I understand individual CLR's run ok in an Application OLTP context, but don't scale up well, when trying to perform bulk jobs due to row-by-row rather than set based operations. fine... cool.. moving on.

I've run Brent Ozar's scripts which identified high free memory as something to look at, as well as a high number of execution plans for each query. The vendor has suggested adding more RAM to the server but this annoys me as it appears the Application is not using what is has now.

What I'm interested in is SQL performance and behavior overall. I see a range of symptoms which suggest something isn't right but I can't pin it down. It's like the server refuses to run. It's determined to walk.

Very roughly speaking it seems to me about 10Gb of the Memory is being used by the database for caching, about 11gb is free, about 3.5Gb for plan cache, and I can't explain the rest. And I'm a bit unsure about some of the definitions such as free vs reserved vs stolen etc. Are they double counting?

Activity monitor shows this:

When I run this query:

`-- what's happening inside my buffer pool?
SELECT counter_name, instance_name, mb = cntr_value/1024.0
FROM sys.dm_os_performance_counters
WHERE (counter_name = N'Cursor memory usage' and instance_name <> N'_Total')
OR (instance_name = N'' AND counter_name IN
(N'Connection Memory (KB)', N'Granted Workspace Memory (KB)',
N'Lock Memory (KB)', N'Optimizer Memory (KB)', N'Sto

While waiting to hear back on a few questions that I posted in a comment on the Question, I will at least reiterate one of my questions: "What about your current stats leads you to suspect that SQLCLR usage is in any way related to performance issues?"

From what I see of your output, SQLCLR is taking up very little memory. It has 110 MB of physical ram for the ClrProcCache. Ok. That is just slightly over what is being taken up by Object Plans, and is a fraction of the 3.36 GB taken up by SQL Plans. Yes, MEMORYCLERK_SQLCLR has reserved about 6.03 GB (not 6.3 -- need to take values in kb and apply value.0 / 1024 / 1024), but that is a) virtual memory, not physical, and b) barely under half of the 12.40 GB of virtual memory reserved by the Buffer Pool. If you scroll over to the Virtual_Memory_committed_kb field, you will see that MEMORYCLERK_SQLCLR is only actively using 419.34 MB of virtual memory.

To check current SQLCLR memory usage, you should be able to run:

SELECT * FROM sys.dm_clr_appdomains;

and look at the survived_memory_kb field (not the total_allocated_memory_kb field as that should be cumulative allocations, regardless of what has been freed up).

To try answer your three questions:

-

What is "holding" my 11.46 GB of "free" memory?

Why do you suspect that anything is "holding" it? You have given SQL Server 27 GB of physical RAM to use. It will use what it wants when it wants to.

-

It appears to me that my ERP system is only using about 10Gb or about 1/3rd of the memory

I think this value is a miscalculation. You stated that while the server has 32 GB of physical RAM, you only allocated 27 GB to SQL Server. If 10 GB were the actual total, that would equate to about 37%. But that isn't the actual total. If you look at the pages_kb field of your final query (against sys.dm_os_memory_clerks) you need to add up all of those rows, which comes out to: 15,424,008 kb. Then doing SELECT 15424008.0 / 1024 / 1024; we get 14.71 GB of RAM being used, out of 27 GB. If we reduce the 27 GB of RAM given to SQL Server by the 11.46 GB of "free" memory, that leaves 15.54 GB that should be "used". We see that 14.71 GB is being used, but that is based on doing a TOP (12) on the query to get the amount of memory used. I suspect that the 0.83 GB difference is hidden in the filtered rows, and so removing the TOP (12) would give us a number much closer to 15.54 GB. And in that case, the amount of memory "used" is approximately 58% of the allowed physical RAM.

-

MEMORYCLERK_SQLCLR has reserved 6.3Gb of memory.

Not exactly. There is 6.03 GB of virtual memory reserved, not physical RAM. Also, as stated above, this is reserved, not committed virtual memory.


Is this normal behavior for CLR's?

I'm not entirely sure about "normal", but I have certainly seen SQLCLR prefer virtual memory for storing large collections.


When do they reserve memory? When they're compiled/ registered/ executed?

What you are looking at should be run-time memory. Since it is reserved, I am guessing that at one point in time an operation needed that much memory and so the reserved size grew to accommodate it. But your query also shows that currently, only 419.34 MB of that 6.03 GB is being used.


Do they ever release it?

At the very least upon service restart. But possibly sooner than that. I have seen it hold onto the reserved space for a long time, but I don't spend too much time checking to see if/when it gets released.

If you are concerned that Garbage Collection isn't running, or not as frequently as you would prefer, you can call it manually by creating a simple Assembly that contains a single function that calls the GC class. If you load that into the same Database as the other Assembly(ies) and make sure that it has the same owner (i.e. the AUTHORIZATION clause of CREATE ASSEMBLY ; verify via SELECT * FROM sys.assemblies; and making sure that principal_id matches), then it will use the same AppDomain.


Is this within the 'free memory'?

No. The "free" memory is referring to the amount of unused physical RAM that SQL Server has been allowed to use via "Max Server Memory". The 6.03 GB of reserved virtual memory is in a swapfile / pagefile.

-

Will flushing the cache help?

Well, how are you going to do that, exactly? If you mean by executing DBCC FREESYSTEMCACHE('ALL');, then it should unload ALL AppDomains, though I am not sure if the Virtual Memory always gets released. I don't see any harm in trying it, at least once, to see the actual effect. I certainly wouldn't make a habit of it, though, as the system will incur the start-up cost of recreating the AppDomain, loading the Assembly (or Assemblies), and whatever else it stores in the ClrProcCache.

-

Are there any features I could be using to influence the above behavior?

Not that I am aware of. And I don't think you would want SQL Server to use up all available memory as that will leave nothing le

StackExchange Database Administrators Q#143445, answer score: 8