Table-Valued Parameter Performance

Dan Guzman shows that setting TVP column sizes correctly can have a major performance impact:

LOB values are especially problematic when a trace captures the RPC completed event of a TVP query. Tracing uses memory from the OBJECTSTORE_LBSS memory pool to build trace records that contain TVP LOB values. From my observations of the sys.dm_os_memory_clerks DMV, each LOB cell of a TVP requires about 8K during tracing regardless of the actual value length. This memory adds up very quickly when many rows and lob columns are passed via a TVP with a trace running. For example, the 10,000 row TVP with 10 LOB columns used in the earlier test required over 800MB memory for a single trace record. Consider that a large number of TVP LOB cells and/or concurrent TVP queries can cause queries to fail with insufficient memory errors. In extreme cases, the entire instance can become unstable and even crash under due to tracing of TVP queries.

This is a must-read if you use TVPs in your environment.

Related Posts

Performance Tuning TVFs With Optional Parameters

Arvind Shyamsundar walks us through a scenario with user-defined functions with optional parameters: If you notice carefully, the above query is an example of ‘optional parameters’ wherein the same query caters to situations where there are specific values for the parameters as well as other cases where there are none. Due to the implementation of […]

Read More

Non-Cost-Based Optimizations In Relational Databases

Lukas Eder has a big article on ten query optimizations that don’t involve looking at statistics or query costs: This optimisation is really silly, but hey, why not. If users write impossible predicates, then why even execute them? Here are some examples: -- "Obvious" SELECT * FROM actor WHERE 1 = 0 -- "Subtle" SELECT […]

Read More


August 2016
« Jul Sep »