Temp table caching improvement for table valued parameters in SQL Server 2012

I wanted to point out a nice performance improvement related to table valued parameters (TVP) in SQL Server 2012.   It’s not currently documented in our online documentation.   But we have had customers who inquired about this.

When you use TVP, SQL Server internally uses temp table to store the data.  Starting SQL Server 2005, temp tables can be cached for re-used.  Caching reduces contentions such as page latch contentions on system tables which can occur as temp tables are created and dropped at a high rate.

If you use TVP with a stored procedure, temp table for the TVP will be cached since SQL Server 2008 when TVP was introduced. 

But if you use TVP together with parameterized queries, temp tables for TVP won’t be cached in SQL 2008 or 2008 R2.   This leads to page latch contentions on system tables mentioned earlier.

Starting SQL Server 2012, table tables for TVP are cached even for parameterized queries.

Below are two perfmon results for a sample application that uses TVP in a parameterized query.   Figure 1 shows that SQL 2008 R2 had sustained a high “temp table creation rate” until the test is complete.  Figure 2 shows that SQL 2012 had just a very quick spike for “temp table creation rate” but then it went to zero while running rest of the test.

Figure 1:  SQL Server 2008 R2’s Temp table Creation Rate

Figure 2:  SQL Server 2012’s Temp Table Creation Rate

Just on a side note, a parameterized query uses sp_executesql from SQL Server perspective.  From application perspective, the following ADO.NET pseudo-code will generate parameterized query:

SqlCommand cmd  = ….;
cmd.CommandText = "SELECT Value FROM @TVP"
cmd.CommandType = System.Data.CommandType.Text;
DataTable tvp  = new DataTable();
//adding rows to the datatable
SqlParameter tvpParam = cmd.Parameters.AddWithValue("@MyParameter", tvp);
tvpParam.SqlDbType = SqlDbType.Structured;

Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support