SQL Server 2012, SQLCLR and Max Server Memory

Published On: 2012-06-11By:

When SQLCLR was first introduced in SQL Server 2005, one of the big complaints that I had with it was that SQLCLR could technically steal all the memory away from the SQL Server’s buffer pool by simply growing it’s memory pool which would cause the OS to request memory back from the SQL Server taking memory away from the buffer pool and the execution plan cache.  This could result, depending on the amount of memory that the SQLCLR needed, the entire memory for the server being eaten up by the SQLCLR component of Microsoft SQL Server, eventually possibly taking all the memory on the entire server for the SQLCLR component.

With the release of SQL Server 2012 this problem goes away to some extent as SQL Server 2012 had many memory architecture related changes put into the product.  Among the variety of changes which were introduced in SQL Server 2012 is the fact that the SQLCLR feature now gets its memory via the traditional SQL Server memory manager.  One of the side effects of this is that the SQLCLR how falls under the control of the “max server memory” setting which is accessed through the sp_configure system stored procedure.

While this doesn’t give you the ability to control how much memory the SQLCLR component specially will take this does mean that you can via the “max server memory” setting keep the SQLCLR processes from taking over all the memory within the Windows OS.

Because of this change in how memory is controlled for the SQLCLR component when upgrading from a prior version of Microsoft SQL Server to SQL Server 2012  the value which is used for the “max server memory” setting will need to be re-thought out as we no longer need to leave room outside the setting for the SQLCLR.  As an example, if a Windows Server with a single instance of Microsoft SQL Server 2008 had 64 Gigs of RAM installed and the instance was a heavy user of SQLCLR it might have made sense to configure the SQL Server instance with 50 Gigs of memory as the max server memory leaving about 4 Gigs for the Windows OS, and 10 Gigs for the SQLCLR.  When this server is upgraded to SQL Server 2012 you would then see that the memory usage for the Windows OS would drop leaving a large amount of memory un-used as the memory which was before allocated outside of the “max server memory” setting will sit there un-used by the SQLCLR.  As this memory is within the “max server memory” setting the buffer pool and procedure cache will now be reduced by the amount of memory that the SQLCLR needs to used below the 50 Gig limit, potentially reducing the amount of memory by 10 Gigs of more.

The upside to this configuration change of the way that the SQLCLR memory is managed is that if the SQLCLR only needs 1 Gig, and before it was being allocated 10 Gigs of memory (as discussed in the example above), it now only takes 1 Gig of memory.  However on the down side is that there is no knob to turn to limit the amount of memory which the SQLCLR can use at the high end.

Denny


Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link