A couple of people reported problems with SQL Server 2008's table-valued parameters and it eventually got back to me. Had to try it out. The reported problem is that there's a plan compile each time TVPs are used from ADO.NET. Interestingly, when the same statements are executed from SSMS (which is an ADO.NET client program), the plan compiles don't occur. I'm able to repro this behavior on SQL Server 2008 SP2, 2008 R2, and Denali CTP1, as well as ADO.NET 3.5 SP1 and 4.0 clients.
Nota bene: This is an excellent reason *not* to test T-SQL only on SSMS (or consider a test on SSMS or SQLCMD to be good-enough coverage) unless all the users of your application will also use SSMS for all their data access. 😉
It turns out that that when you use a TVP (either in ADO.NET or SSMS) the following code is reported by SQL Profiler:
declare @t your_table_type –say your table type has a single bigint column
insert @t values(1)
insert @t values(2) — one per row
exec your_proc @t
When this code is executed by ADO.NET (I'm also able to repro this with ODBC, I'm looking for my OLE DB TVP example) it executes as an RPC call. When issued from an SSMS query window the same pattern of statements execute as a SQLStmt call. With a SQLStmt call, both the query plan for the batch (inserts + exec) and the query plan for the stored procedure are cached and reused. When it executes as a RPC call, only the query plan for the stored procedure is cached. The plan for the insert call is NEVER cached. So, if you execute the same call 100 times with ADO.NET/ODBC you get 100 plan compilations. The spike is visible in Perfmon looking at SQL Compilations:Sec. Looking at the plan cache confirms this behavior.
BTW this is a TVP-specific behavior. With other parameter types, there are only declare variable(s), initialize, and RPC call, and RPC is the preferred way to do this call. Clear back to Ken Henderson's original writings on RPC vs SQLBatch. That's why the APIs do it that way.
With SQLProfiler capturing Showplan XML Statistics Profile, RPC:Starting/Completed, SQL:BatchStarting/Completed, and all the cache events, I see (in this order) for the APIs:
Showplan XML (for the 2 inserts)
RPC:Starting
SP:CacheInsert (for the proc)
Showplan XML (for the proc)
RPC:Completed
Even subsequent execution generates a new plan for the inserts and a CacheHit for the proc.
Executing the same statements from SSMS:
SP:CacheInsert (for the batch)
SQL:BatchStarting
Showplan XML (for the batch of 2 inserts)
Showplan XML (for the batch of 2 inserts)
SP:CacheInsert (for the proc)
Showplan XML (for the proc)
SQL:BatchCompleted
Now, to look at how the statement is executed in ADO.NET. There are three possible ways:
1. Execute as a CommandType.StoredProcedure with a TVP param of SqlDbType.Structured
2. Execute as a CommandType.CommandText with the text "exec your_proc @p" with a TVP param of SqlDbType.Structured
3. Execute as a CommandType.CommandText with the text "declare @t your_table_type;insert @t values(1);insert @t values(2);exec your_proc @t" and no parameters associated with the SqlCommand object.
Case 1 is submitted as an RPC call, no cache on the insert plan
Case 2 is translated into sp_executesql, submitted as an RPC call, no cache on the insert plan
Case 3 replicates SSMS behavior, submitted as SQLBatch, caching on the insert/batch occurs
Moreover there are three possible ways to produce the parameter for cases 1 and 2:
1. Use a DataTable
2. Use a DataReader
3. Use a collection (I used List<T>) of SqlDataRecord
There is NO difference in the cache behavior based on which way I produce the parameter.
BTW, for the ODBC afficianados, I'm using SqlExecDirect "{CALL my_proc ?}" and parameters produced with SQLBindParameter and moving the parameter focus for the TVP parm.
That leaves use with the less-than-happy occurance that, to acheive query plan reuse of both plans, we must built up a SQL statement (case 3 above) in code. None of the cool SqlDbType.Structured ways produce query plan resue. Oh.
So, I got out my friendly TDS spec to attempt to determine why. Although there are a lot of new packet types for TVP support, there's nothing that validates the behavior I'm seeing. If anyone knows for sure that this is expected behavior or that the Profiler, cache DMVs, and Perfmon are all being confused because of the new protocol packets and mis-reporting things, I'd prefer to hear that. Else, I'll be filing a connect bug soon.
Note: I did file a bug and it was resolved as "by design". Read the reprise here.
@bobbeauch