A few months ago, I published a blog entry entitled "The interesting case of TVPs and plan compilation" about the fact that a plan compile is reported (by perfmon) each time you correctly use a TVP from a program in RPC mode. After waiting a while, I published a connect bug.
Turns out that this behavior is by design. Here's the response from the bug report:
"Table valued parameter is essentially a table variable. When a TVP call to a SP is executed, you will see 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. So you would see it for every invocation of the SP call. The actual compilation overhead for this insert statement is minimal and should not impact overall execution of the SP. In case of the T-SQL sample with multiple inserts, it is treated as regular T-SQL statements so the batch behavior is different. Hope this explains the issue."
So it IS by design and the behavior of a TVP, properly used in an ADO.NET program as SqlDbType.Structured (its a bit code different in OLE DB and ODBC but same result) is similar to bulk insert. Nice. And the compilation overhead is minimal (it is reported as a trivial plan in the plan XML) and (most likely) the speed of the special codepath more than makes up for the compile.
My only worry is that automated reporting tools or DBA scripts that watch perfmon for plan compiles may not understand what they are seeing. And it may be reported on forums that "TVP use causes excessive recompilations" (as it already has been). If you see such reports, point them at this blog post or the associated connect item.
@bobbeauch