More on Table-valued Parameters (TVP’s)

While finalising some slides for my presentation at the Canberra .Net User Group on Using XML and TVP’s to Transport Relational Data, I stumbled across a couple of posts by Bob Beauchemin relating to how TVP’s cause plan recompiles when called from ADO.NET.  Given that I published a post back in May 2011 about using TVP’s with ADO.NET, I thought I’d provide a brief overview of what Bob is referring to (you can find Bob’s original content links below).

Overview

Let’s assume we have a Table Type called udtt_Passenger which contains 1 column of type BIGINT and a stored procedure called usp_AddPassenger which has one parameter of type udtt_Passenger.  Let’s also assume that we have a SQL Server Table Variable and an ADO.NET Datatable each containing 5 rows of data.  If we were to call our stored procedure from SSMS (passing in our SQL Server Table Variable) and ADO.NET (passing in our ADO.NET Datatable), SQL Profiler would report the following:

declare @t [Person].[udtt_Passenger]
insert @t values(1)
insert @t values(2)
insert @t values(3)
insert @t values(4)
insert @t values(5)
exec usp_AddPassenger @t

Although both calls result in the exact same code being reported in SQL Profiler, it’s the mechanism used to do the call that’s different.

When SSMS executes this code, it does so via a SQLStmt call so the code is executed as a batch i.e. INSERTS + EXEC therefore a query plan for the batch and stored procedure is cached and reused .  When ADO.NET executes this code, it does so via a RPC call therefore only the query plan for the stored procedure is cached, not the INSERTS.  It is this difference that causes plan compiles to occur for each INSERT statement when using ADO.NET.

Anyway, to cut a long story short, this is by design and according to Microsoft, has minimal impact on performance (SQL Connect Item submitted by Bob).

References

Advertisements

About Mr. Wharty's Ramblings
Jeff holds a Master’s Degree in Systems Development (.NET Stream) and a Master’s Degree in Database Design and Management (SQL Server). Jeff is also a MCT specialising in SQL Server technologies and holds MCPD, MCITP, MCDBA and MCSD certifications.

12 Responses to More on Table-valued Parameters (TVP’s)

  1. Dan Guzman says:

    Hi, Jeff.

    I’ve tried to reproduce this ADO.NET caching behavior without success. The C# console app below shows the RPC method I’d typically use to call a proc with a TVP. Can you please post a C# code snippet that demonstrates the issue you and Bob describe? I must be missing something.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using Microsoft.SqlServer.Server;

    namespace TVPTest
    {
    class Program
    {
    static void Main(string[] args)
    {
    var connection = new SqlConnection(@”Data Source=MyServer;Initial Catalog=Test;Integrated Security=SSPI”);
    connection.Open();
    var command = new SqlCommand(@”dbo.usp_AddPassenger”, connection);
    command.CommandType = CommandType.StoredProcedure;

    var sqlDataRecordIenumerable = new PassengerList();
    command.Parameters.Add(“@tvp”, SqlDbType.Structured).Value = sqlDataRecordIenumerable;
    command.ExecuteNonQuery();
    connection.Close();
    Console.WriteLine(“Done”);
    }
    }

    ///
    /// IEnumerable for TVP
    ///
    internal class PassengerList : List, IEnumerable
    {
    private SqlMetaData[] metaData = new SqlMetaData[1]
    {
    new SqlMetaData(“PassengerNumber”, SqlDbType.BigInt)
    };

    private int passengerCount = 5;

    ///
    /// return 5 passengers
    ///
    public new IEnumerator GetEnumerator()
    {
    var row = new SqlDataRecord(metaData);

    for (Int64 i = 0; i < passengerCount; ++i)
    {
    row.SetInt64(0, i + 1);
    yield return row;
    }

    }
    }
    }

    Thanks,

    Dan

  2. Mr. Wharty's Ramblings says:

    Hi Dan,

    It’s not an ADO.NET Caching problem. It’s a SQL Server Plan Cache issue.

    You will need to use SQLProfiler and capture the following to see what is going on:

    Showplan XML Statistics Profile
    RPC:Starting/Completed
    SQL:BatchStarting/CompletedA
    All the cache events

    You should see something similar to the following;

    Showplan XML (for the 5 inserts)
    RPC:Starting
    SP:CacheInsert (for the proc)
    Showplan XML (for the proc)
    RPC:Completed

    Bob’s post shows more detail of what to look for http://sqlskills.com/BLOGS/BOBB/post/The-interesting-case-of-TVPs-and-plan-compilation.aspx

    Cheers
    Jeff

    • Dan Guzman says:

      I should have been clearer, Jeff. I read Bob’s post a couple of times and landed here when searching for more info.

      I ran a Profiler trace including the following events:

      Audit Login
      Audit Logout
      Showplan XML Statistics Profile
      SP:CacheHit
      SP:CacheInsert
      SP:CacheMiss
      SP:CacheRemove
      SQL:BatchCompleted
      SQL:BatchStartomg
      RPC:Starting
      RPC:Completed

      I used the following T-SQL script to setup the test:

      CREATE DATABASE Test;
      GO
      USE Test;
      GO
      CREATE TYPE dbo.udtt_Passenger AS TABLE(PassengerNumber bigint);
      GO
      CREATE TABLE dbo.Passenger(PassengerNumber bigint NOT NULL);;
      GO
      CREATE PROC dbo.usp_AddPassenger
      @tvp dbo.udtt_Passenger READONLY
      AS
      INSERT INTO dbo.Passenger(PassengerNumber)
      SELECT PassengerNumber FROM @tvp;
      GO

      When I ran the code I posted earlier (source was somewhat manged by the http scrubbing), the first time, I saw:

      Audit Login
      Showplan XML Statistics Profile
      RPC:Starting
      SP:CacheInsert
      Showplan XML Statistics Profile
      RPC:Completed
      Audit Logout

      When I run the code the second time, I saw:

      Audit Login
      Showplan XML Statistics Profile
      RPC:Starting
      SP:CacheHit
      Showplan XML Statistics Profile
      RPC:Completed
      Audit Logout

      The RPC:Completed text shows:

      declare @p1 dbo.udtt_Passenger
      insert into @p1 values(1)
      insert into @p1 values(2)
      insert into @p1 values(3)
      insert into @p1 values(4)
      insert into @p1 values(5)
      exec dbo.usp_AddPassenger @tvp=@p1

      So I’m trying to understand why the RPC is cached in my unsuccessful repro, which is contrary to what you and Bob see. I must be doing something different as I’m not able to reproduce the issue.

      Thanks,

      Dan

      • Mr. Wharty's Ramblings says:

        Hi Dan,

        Each time I run your example via Visual Studio, I get the following in SQL Profiler:

        Showplan XML Statistics Profile
        RPC:Starting
        SP:CacheHit
        Showplan XML Statistics Profile
        RPC:Completed

        The first “Showplan XML Statistics Profile” line relates to a plan being created for the INSERTS. This appears each time the code is executed and consists of the following steps:

        1) Table Valued Function
        2) Compute Scalar
        3) Top
        4) Table Insert [@tvp]

        When I run your example from SSMS, I get the following in SQL Profiler:

        SP:CacheHit
        SQL:BatchStarting
        Showplan XML Statistics Profile
        Showplan XML Statistics Profile
        Showplan XML Statistics Profile
        Showplan XML Statistics Profile
        Showplan XML Statistics Profile
        SP:CacheHit (for the proc)
        Showplan XML (for the proc)
        SQL:BatchCompleted

        The first 5 “Showplan XML Statistics Profile” lines relate to a plan being reused for the INSERTS. They appear each time the code is executed and consist of the following single step:

        1) Table Insert [@p1]

        If the RPC call was reusing a cached plan, it would contain the same “Showplan XML Statistics Profile” lines as the SQLStmt call.

        It’s a bit difficult trying to explain when I can’t show screen shots so I will look at adding some more content to this post over the weekend.

        Cheers
        Jeff

  3. Dan Guzman says:

    Thanks for looking at this, Jeff. Please don’t spend your precious weekend time on this.

    I found the item Bob filed on Connect (https://connect.microsoft.com/SQLServer/feedback/details/648637/using-table-valued-parameters-from-clients-cause-recompiles-with-each-use). It was closed as by design because the TVP is loaded by a “batch first that does the insert into the table variable containing the data passed from the client. This insert statement uses a special code path similar to insert bulk statement and it is not cacheable.”

    The key to my underanding of what’s going on is that there is no T-SQL involved when loading the TVP using an RPC call. Like all RPC calls, the text that Profiler shows is just a fabrication of what the T-SQL might look like if it were run from SSMS. The table-valued-function depicted in the RPC insert plan is simply a way to visualize reading the TDS binary data stream by the special code path Umachandar mentioned in his response to the Connect item.

    Again, thanks for taking the time to get me on the path to better knowledge of this behavior.

  4. Pingback: Link Resource # 32 : DESC 01-DESC 02 « Dactylonomy of Web Resource

  5. Pingback: Link Resource # 33 : DESC 03-DESC 08 « Dactylonomy of Web Resource

  6. Pingback: Link Resource # 34 : DESC 08 – DESC 11 « Dactylonomy of Web Resource

  7. Pingback: Link Resource # 35: Dec 11 – Dec 14 « Dactylonomy of Web Resource

  8. Pingback: Link Resource # 38: Dec 27 – Dec 30 « Dactylonomy of Web Resource

  9. Pingback: Link Resource # 37: Dec 23 – Dec 27 « Dactylonomy of Web Resource

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: