.NET GC not reclaiming memory after List<T>.Clear() in SQL CLR UDF under load - leads to memory insufficiency in SQL Server

We're running into memory insufficiency issues on SQL Server when using a SQL CLR UDF written in C# targeting .NET Framework 4.8. The function uses a singleton class to cache user session info using a List<T> of structs. Each struct holds SessionId(int32), TransactionId(int32), and a string for the username.

Under a stress test with 500 parallel connections, memory usage grows over time and is not reclaimed — even after the list is cleared multiple times and Gen2 GC is confirmed to be running. Eventually, this leads to high memory pressure and SQL Server becomes unresponsive.

Code snippet (simplified for clarity):

public class MyAppLib : Singleton<MyAppLib>
{
	 public struct UserSession
	 {
		 public UInt32 Sessionid;
		 public UInt32 Transactionid;
		 public string User;
		 public UserSession(UInt32 Sessionid, UInt32 Transactionid, string User)
		 {
			 this.Sessionid = Sessionid;
			 this.User = User;
			 this.Transactionid = Transactionid;
		 }
	 }
	 List<UserSession> _sessioninfo = new List<UserSession>();
	 
	 private MyAppLib()
	 {
		_sessioninfo.Clear();
		Initiate();
	 }
	~MyAppLib()
	{
		Terminate();
		_sessioninfo.Clear();
	}
	
	[SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
	private static string __GetContextUser()
	{
		SqlConnection connection = new SqlConnection("context connection=true");
		connection.Open();
		SqlCommand command = new SqlCommand("select SUSER_NAME();", connection);
		string policyMember = command.ExecuteScalar() as string;
		connection.Close();
		return policyMember;
	}
	private const int MYAPP_MAX_SESSION_INFO_MAP_SIZE = 100000;
	private static readonly object lockObject = new object();
	private static int currentIndex = 0;
	 public static void CheckAddSessionInfo(UInt32 ui4SessionId, UInt32 ui4TransactionId, ref string PolicyMember)
	 {
		 MyAppLib myapp = MyAppLib.Instance; //singletone
		 lock (lockObject)
		 {
			 if (myapp._sessioninfo.Count >= MYAPP_MAX_SESSION_INFO_MAP_SIZE)
			 {
				 myapp._sessioninfo.Clear();
			 }
			 int sessionInfoIdx = myapp._sessioninfo.FindIndex(PrevSessionInfoExist => PrevSessionInfoExist.Sessionid == ui4SessionId && PrevSessionInfoExist.Transactionid == ui4TransactionId);
			 if (-1 == sessionInfoIdx)
			 {
				 PolicyMember = __GetContextUser();
				 UserSession SessionObj = new UserSession(ui4SessionId, ui4TransactionId, PolicyMember);
				 myapp._sessioninfo.Add(SessionObj); 
			 }
			 else
			 {
				 PolicyMember = myapp._sessioninfo[sessionInfoIdx].User;
			 }
		 }
	 }
	 
	[SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
	public static SqlBinary MyApp_Udf1(	string arg1,
										  SqlBinary arg2, 
										  SqlInt32 i4sessionid ,
										  SqlInt64 i8transactionid )
	{
		string policyMember = "";
		ui4SessionId = Convert.ToUInt32(i4sessionid.Value);
		i8transactionid = i8transactionid & 0x000000FFFFFFFF;
		ui4TransactionId = Convert.ToUInt32(i8transactionid.Value);
		CheckAddSessionInfo(ui4SessionId, ui4TransactionId, ref policyMember);
		  
		// UDF1 logic...
	}
	
	[SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
	public static SqlBinary MyApp_Udf2(	string arg1,
										  SqlBinary arg2, 
										  SqlInt32 i4sessionid ,
										  SqlInt64 i8transactionid )
	{
		string policyMember = "";
		ui4SessionId = Convert.ToUInt32(i4sessionid.Value);
		i8transactionid = i8transactionid & 0x000000FFFFFFFF;
		ui4TransactionId = Convert.ToUInt32(i8transactionid.Value);
		CheckAddSessionInfo(ui4SessionId, ui4TransactionId, ref policyMember);
		  
		// UDF2 logic...
	}
}

Problem Observed:

• With a threshold of 100000, we call .Clear() when the list is full and then start refilling it.
• After several such clear-and-refill cycles (e.g., after 2000000 entries), memory usage continues to rise steadily.
• With debug logs using GC.CollectionCount(2), we confirmed Gen2 GCs are happening (e.g., 117 → 193), but memory is not reclaimed.
• Eventually, SQL Server hits memory limits or becomes unstable.
• In memory logs, we see high GC.GetTotalMemory(false) and private memory growth, even though the list Count is small or 0 after .Clear().
• We observed the same things with perfview tool also.

Our Understanding:

• .Clear() does not null the backing array — so UserSession structs with string fields still reference old strings.
• These string objects may remain live until overwritten.
• GC does not collect them because the array slots are still holding them.
• This causes memory fragmentation and long-term retention in Gen2/LOH.

What We’re Looking For:

1. Is this expected GC behavior in .NET Framework 4.8 when using List<T>.Clear() on value-type structs with reference fields?
2. Why doesn’t memory drop after Gen2 GC even though .Clear() is called and list Count == 0?
3. What’s the recommended memory-safe caching pattern in SQL CLR for such scenarios? Should we avoid List<T>.Clear() altogether?
4. Is there a better GC-safe way to drop memory (e.g., explicitly nulling, trimming, or using a circular buffer)?

Additional Info:

• Stress tested with 500 connections for ~15 hours
• .NET Framework 4.8 hosted in SQL Server
• Singleton instance holds the list between UDF invocations
• We also tried switching to a circular buffer, but without interning usernames, memory issues persisted
• If we don't clear the list i.e., use unbound list, then we don't observe this memory issue on SQL server.

We'd appreciate official guidance from Microsoft/.NET runtime experts on how to design such caches safely under high concurrency in SQL Server-hosted CLR environments.