TVP導致SQL記憶體暴漲

2020-07-16

單純記錄一下，有用到Table Value Parameter來當參數傳的話，記得程式裡要明確宣告一下String的長度

下面鏈結是PASS幹部Ken Chiu的血淚

https://mssqltaiwan.wordpress.com/2019/07/22/bug-objectstore_lbss/

下面的Code是我LAB時的簡易範例，模擬用TVP大量塞資料，其中需注意的地方就是Column需指定MaxLength就可以有效避開這一個問題(我不是專業碼農，請大家海涵)

        Dim cnStr As String
        Dim cn As SqlConnection
        Dim t As New DataTable

        cnStr = "Data Source=127.0.0.1;User Id=apTest; Password=123;Initial Catalog=dbTest"
        cn = New SqlConnection(cnStr)
        Dim Id, UName, UserId As DataColumn
        Id = New DataColumn("Id", System.Type.GetType("System.Int32"))
        UName = New DataColumn("UName", System.Type.GetType("System.String"))
        UserId = New DataColumn("UserId", System.Type.GetType("System.String"))
        UName.MaxLength = 50
        UserId.MaxLength = 10

        t.Columns.Add(Id)
        t.Columns.Add(UName)
        t.Columns.Add(UserId)
        For i = 0 To 10000 - 1 Step 1
            t.Rows.Add(i, "Rock", "A123456789")
        Next
        Dim cmd As SqlCommand = cn.CreateCommand
        Dim pTVP As SqlParameter
        cn.Open()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(New SqlParameter("@Result", ""))
        pTVP = cmd.Parameters.Add("@UserList", SqlDbType.Structured)
        pTVP.Value = t
        pTVP.TypeName = "tvp_tbTarget"
        cmd.CommandText = "Usp_UpdateBatch"
        Dim reader As SqlDataReader
        reader = cmd.ExecuteReader()

        cmd.Dispose()
        cmd = Nothing
        cn.Close()
        cn.Dispose()
        cn = Nothing