Table valued parameters (TVP) are a great feature that solves the problem of passing a table of values into a stored procedure.  In the past problems like this would get solved by complicated approaches such as passing and parsing XML, or passing long streams of dynamically generated SQL.  There is a caveat you should be aware of with regards to performance when using TVPs.  In this post I’ll describe what this is and how you can easily avoid problems as a result of this.

How can using TVPs negatively affect my performance?

This is pretty simple – they have no or inaccurate statistics.  As a result the optimizer has difficulty coming up with an efficient execution plan.

How do I get around this?

Simple.  Insert the values into a #temp table prior to using them.  The overhead of adding an extra step involving a #temp table will likely outweigh the problems that joining to a TVP can cause.  Keep in mind I’m suggesting using a #temp table rather than an @table variable.  I have another post here explaining why.

Conclusion

I see problems caused by using TVP’s in joins and merges on a pretty regular basis (there are cases where this causes issues with spools on inserts as well).  When I suggest to the developers they add an extra step which involves a #temp table they are usually skeptics; however, in almost every case I’ve suggested this there has been a pretty positive impact.  Good luck tuning!

See how SQLGrease can quickly get you to the bottom of a query timeout error.