SQL Server Home
Using table-valued parameters from clients cause recompiles with each use
as By Design
3/1/2011 10:27:20 AM
User(s) can reproduce this bug
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.
SQL Server 2008 - Developer Edition
Windows Server 2008
Operating System Language
Steps to Reproduce
See blog entry: http://www.sqlskills.com/BLOGS/BOBB/post/The-interesting-case-of-TVPs-and-plan-compilation.aspx and Forum Entry: http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/9d498061-5cbf-49d7-83ea-72f5ddf5db6c
TVP should not cause recompile with every call, as is the case when TVPs are used with SQLStmt. RPC (explicit parameters) is the documented way to use this feature.
TVP does cause recompile with each call.
to post a comment.
Please enter a comment.
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.
on 3/11/2011 at 6:18 PM
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
to post a workaround.
Please enter a workaround.
© 2014 Microsoft