Using table-valued parameters from clients cause recompiles with each use - by Bob Beauchemin

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 648637 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 3/1/2011 10:27:20 AM
Access Restriction Public


Each time a table-valued parameter is used from a database API (ADO.NET, ODBC, etc) using the appropriate parameter-binding methods, recompiles occur. These are caused by RPC-code that fills in the TVP on the server side. Using SSMS or SQLCMD does not cause these recompiles because SQL-Stmt path (rather than RPC) is used. This happens in all database APIs, all versions of SQL Server since the feature was introduced.

This has lead some users to conclude that, because of the recompiles involved, the feature should not be used. 
Sign in to post a comment.
Posted by Bob Beauchemin on 3/13/2011 at 3:11 AM
Thanks, UC, for the explanation. I'm going to post an update/addedum to the blog entry.
Posted by Umachandar [MSFT] on 3/11/2011 at 6:18 PM
Hi Bob,
Thanks for your feedback. The behavior you are seeing is by design. 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.

Umachandar, SQL Programmability Team