Memory Issue with Simple CLR Based Table-Value-Function (TVF)

Memory Issue with Simple CLR Based Table-Value-Function (TVF)

• wirwin

  Steven,

  Thanks for your reply. I have enclosed the C# function below.   The function is mostly called by an input string that is shorter than 2000 characters and with the tab character as the delimter.

  Wenbin

   public class StringFunction
   {
   [SqlFunction(DataAccess = DataAccessKind.None,
     FillRowMethodName = "FillRowForSplit",
     IsDeterministic = true, IsPrecise = true)]
   public static IEnumerable Split(string inputString, string delimiter)
   {
    if (inputString == null)
    {
    return new DataView();
    }
  
    string[] strArray = inputString.Split(new string[1] { delimiter },
         StringSplitOptions.None);
  
    DataTable dt = new DataTable();
    dt.Columns.Add("Idx", typeof(Int32));
    dt.Columns.Add("Value", typeof(string));
  
    for (int i = 0; i < strArray.Length; i++)
    {
    DataRow dr = dt.NewRow();
    dr["Idx"] = i;
    dr["Value"] = strArray.Trim();
  
    dt.Rows.Add(dr);
    }
  
    return dt.DefaultView;
   }
  
   // FillRow method that returns column values for specified ordinal.
   public static void FillRowForSplit(object obj, out int idxCol, out string valueCol)
   {
    DataRowView drv = (DataRowView)obj;
    idxCol = (int)drv["Idx"];
    valueCol = (string)drv["Value"];
   }
   }
  

  Here is the SQL function declaration:

  CREATE FUNCTION dbo.Split(@InputString NVARCHAR(MAX), @Delimiter NVARCHAR(MAX))
  RETURNS TABLE (Idx INT, Value NVARCHAR(200))
  AS
  EXTERNAL NAME MySQLUtility.[MySQLUtility.StringFunction].Split
  GO
  

  
  
  

• Why_did_I_go_64-bit?

  Hi Wenbin,

  I was wondering if you have managed to solve the problem as i'm facing same issue but not sure which clr function might be causing it. We are using SQL server 2005 SP2.

  thanks

  
  

• MingLin

  Wenbin,

  Can you give more information on how often this happens What's the load of the server when this happens Does this problem persists or it fixed itself when you hit this problem once a while

  I'd appreciate if you can provide such information on SQL 2005 RTM build.

  thanks

  xiaowei

  
  

• tbcarver

  Xiaowei,

  Thanks for the reply.  I am not sure how often it occurs, but it depends on how much the SQL CLR function is called.  AppDomain unload and recreate happens every a few seconds when the problem occurs as shown by the SQL server log entires below.

  Date    1/6/2006 2:57:11 AM
  Log     SQL Server (Current - 1/6/2006 2:57:00 AM)
  Source  spid1s
  Message AppDomain 1007 (MyDB.dbo[runtime].1008) unloaded.
  
  Date 1/6/2006 2:57:11 AM
  Log SQL Server (Current - 1/6/2006 2:57:00 AM)
  Source spid1s
  Message AppDomain 1007 (MyDB.dbo[runtime].1008) is marked for unload due to memory pressure.
  
  Date 1/6/2006 2:57:05 AM
  Log SQL Server (Current - 1/6/2006 2:57:00 AM)
  Source spid66
  Message AppDomain 1007 (MyDB.dbo[runtime].1008) created.
  
  
  Date 1/6/2006 2:57:04 AM
  Log SQL Server (Current - 1/6/2006 2:57:00 AM)
  Source spid1s
  Message AppDomain 1006 (My.dbo[runtime].1007) unloaded.
  
  Date 1/6/2006 2:57:04 AM
  Log SQL Server (Current - 1/6/2006 2:57:00 AM)
  Source spid1s
  Message AppDomain 1006 (MyDB.dbo[runtime].1007) is marked for unload due to memory pressure.
  
  Date 1/6/2006 2:56:55 AM
  Log SQL Server (Current - 1/6/2006 2:57:00 AM)
  Source spid66
  Message AppDomain 1006 (MyDB.dbo[runtime].1007) created.
  

  The problem does not appear to be related to the load on the server. The same Source SPID (spid66) is logged for the hundreds of consecutive log entries (as the two shown above).

  The problem has not reached to a state that we have to restart the SQL server service yet (that was the case for for JuneCTP).  

  I can provide more details if necessary.

  Thanks, Wenbin

  
  
  

• dev88

  Can you either provide the source code for your C# TVF or give some more information about it Specifically, how large are the strings and how are you passing them (nvarchar(max)) What are you using to split the string (String.Split, Regex, custom..)
  
  
• Memory Issue with Simple CLR Based Table-Value-Function (TVF)