CLR Memory Management in SQL Server

All we need is an easy explanation of the problem, so here it is.

I m using SQL server 2012 SP3 , I want to know if CLR is using Max server memory(bufferpool) or it is using memory out of max server memory because if I check Memoryclerk , seems SQLCLR have separate row as below. I saw one page mentioned from SQL Server 2012, CLR is included in max server memory. Please advice

CLR Memory Management in SQL Server

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Reference: Memory Management Architecture Guide

Changes to Memory Management starting with SQL Server 2012 (11.x)

In earlier versions of SQL Server ( SQL Server 2005 (9.x), SQL Server
2008 and SQL Server 2008 R2), memory allocation was done using five
different mechanisms:

Single-Page Allocator (SPA), including only memory allocations that
were less than, or equal to 8-KB in the SQL Server process. The max
server memory (MB) and min server memory (MB) configuration options
determined the limits of physical memory that the SPA consumed. The
Buffer Pool was simultaneously the mechanism for SPA, and the largest
consumer of single-page allocations.

Multi-Page Allocator (MPA), for
memory allocations that request more than 8-KB.

CLR Allocator,
including the SQL CLR heaps and its global allocations that are
created during CLR initialization. Memory allocations for thread
stacks in the SQL Server process.

Direct Windows allocations (DWA),
for memory allocation requests made directly to Windows. These include
Windows heap usage and direct virtual allocations made by modules that
are loaded into the SQL Server process. Examples of such memory
allocation requests include allocations from extended stored procedure
DLLs, objects that are created by using Automation procedures (sp_OA
calls), and allocations from linked server providers.

Starting with
SQL Server 2012 (11.x), Single-Page allocations, Multi-Page
allocations and CLR allocations are all consolidated into a "Any size"
Page Allocator, and it’s included in memory limits that are controlled
by max server memory (MB) and min server memory (MB) configuration
options. This change provided a more accurate sizing ability for all
memory requirements that go through the SQL Server memory manager.

Max server memory controls the SQL Server memory allocation, compile memory, all caches (including the buffer pool), query execution memory grants, lock manager memory, and CLR memory (essentially any memory clerk found in sys.dm_os_memory_clerks).

That does not mean all memory required for CLR execution is controlled by max server memory settings.DLL by a third party (not published by Microsoft) will still use memory that is NOT accounted for within max server memory setting.

Memory for thread stacks, CLR, extended procedure .dll files, the
OLE DB providers referenced by distributed queries, automation objects
referenced in Transact-SQL statements, and any memory allocated by a
non SQL Server DLL are not controlled by max server memory
.

I suggest reading this blog post by Jonathan Kehayias especially the comments.

Effects of min and max server memory on SQL CLR Q&A confirms the above assertion.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply