Environment
Single server, Win2k3 R2 x64 runing SQL 2k8 x64 with 8GB.
We started noticing SQL was consuming anywhere from 2GB to 5GB of memory anytime a report from SQL Reporting Services was being run. The number jump tremendously after we ran the report Software 1A – Summary of Installed Software in a Specific Collection but this was expected since the report was pulling from the view v_GS_Installed_Software_Categorization, a normalized view of the data in v_GS_installed_Software. More inforation on the Asset Intelligence views can be found here https://technet.microsoft.com/en-us/library/dd334669.aspx
We had the SQL database admin take a look at what exactly whas eating up all the memory and we saw that CLR was taking up almost 50% of the memory. I had not ever configured or done anything with CLR before.
For those of you who are not aware, CLR stands for Common Language Runtime, a component of .NET. I had never know before that CLR was even in play. CLR has to be enabled on the SCCM Site database. More information on CLR can be found here https://en.wikipedia.org/wiki/Common_Language_Runtime
The reason being is that a check is done by the Management Point site system, particularly the Management Point control manager to detect the configuration of CLR. The following entries are logged in the MPControl.log:
Checking the current CLR Enabled configuration setting for the configured SQL Server hosting the database. $$<SMS_MP_CONTROL_MANAGER><Fri Jul 17 14:30:33.805 2009 Pacific Daylight Time><thread=6428 (0x191C)>
Getting the CLR Enabled value from the configured SQL database. $$<SMS_MP_CONTROL_MANAGER><Fri Jul 17 14:30:33.805 2009 Pacific Daylight Time><thread=6428 (0x191C)>
Attempting to connect to the configured SQL database. $$<SMS_MP_CONTROL_MANAGER><Fri Jul 17 14:30:33.822 2009 Pacific Daylight Time><thread=6428 (0x191C)>
The Management Point Control Manager uses CLR to connect to the site database.
We wanted to turn CLR off to reduce the memory consumption of SQL but CLR needs to be enabled on the SCCM Site database. We will instead be upgrading the memory on the server to 16GB to accomodate future growth and performance trends.
The other thing to point out is that the configuration of CLR is checked when you perform an upgrade to a new service pack in SCCM.
Sources:
https://www.windows-noob.com/forums/index.php?/topic/693-guide-moving-your-site-database/
