How TVP Affect SQL Server Memory

之前有寫過一篇文章，其中的Root Cause就是TVP+SQL Profiler所造成的記憶體內存問題。原本以為tvp中的欄位如果沒有varchar(max)理應不會對SQL Server造成問題。但在測試結果下，如果Application沒有對字串限制長度，還是會造成上述的記憶體內存問題。同時也感謝Rock撥空測試幫助我驗證以下結論。

大概有幾個手段可以解決問題

1. 限制Application 字串長度
   
2. 升級CU Hotfix

//Sample Code for limit string length
DataSet dataSet = new DataSet();

DataTable customTable = new DataTable();
DataColumn dcName = new DataColumn("Name", typeof(string));
dcName.MaxLength= 500;
customTable.Columns.Add(dcName);

dataSet.Tables.Add(customTable);

至於原理跟測試，有興趣的可以看這篇文章。

DataTable objects are most commonly used as TVP values. DataTables are easy to use and can serve as containers for data beyond just TVP usage. But unlike DbDataReader and IEnumerable objects, a big gotcha with a DataTable is that the default data type String with maximum length of -1 (2GB LOB). This is the .NET equivalent of the SQL Server nvarchar(MAX) data type and has many insidious and negative implications with a TVP.