{"id":577,"date":"2010-12-23T13:49:00","date_gmt":"2010-12-23T13:49:00","guid":{"rendered":"\/blogs\/bobb\/post\/The-interesting-case-of-TVPs-and-plan-compilation.aspx"},"modified":"2014-01-20T12:19:32","modified_gmt":"2014-01-20T20:19:32","slug":"the-interesting-case-of-tvps-and-plan-compilation","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/the-interesting-case-of-tvps-and-plan-compilation\/","title":{"rendered":"The interesting case of TVPs and plan compilation"},"content":{"rendered":"

\nA couple of people reported problems<\/a> with SQL Server 2008's table-valued parameters and it eventually got back to me. Had to try it out. The reported problem is that there's a plan compile each time TVPs are used from ADO.NET. Interestingly, when the same statements are executed from SSMS (which is an ADO.NET client program), the plan compiles don't occur. I'm able to repro this behavior on SQL Server 2008 SP2, 2008 R2, and Denali CTP1, as well as ADO.NET 3.5 SP1 and 4.0 clients.\n<\/p>\n

\nNota bene: This is an excellent reason *not* to test T-SQL only on SSMS<\/strong> (or consider a test on SSMS or SQLCMD to be good-enough coverage) unless all the users of your application will also use SSMS for all their data access. \ud83d\ude09\n<\/p>\n

\nIt turns out that that when you use a TVP (either in ADO.NET or SSMS) the following code is reported by SQL Profiler:\n<\/p>\n

\ndeclare @t your_table_type –say your table type has a single bigint column
\ninsert @t values(1)
\ninsert @t values(2) — one per row
\nexec your_proc @t\n<\/p>\n

\nWhen this code is executed by ADO.NET (I'm also able to repro this with ODBC, I'm looking for my OLE DB TVP example) it executes as an RPC call. When issued from an SSMS query window the same pattern of statements execute as a SQLStmt call. With a SQLStmt call, both the query plan for the batch (inserts + exec) and the query plan for the stored procedure are cached and reused. When it executes as a RPC call, only the query plan for the stored procedure is cached. The plan for the insert call is NEVER cached. So, if you execute the same call 100 times with ADO.NET\/ODBC you get 100 plan compilations. The spike is visible in Perfmon looking at SQL Compilations:Sec. Looking at the plan cache confirms this behavior.\n<\/p>\n

\nBTW this is a TVP-specific behavior. With other parameter types, there are only declare variable(s), initialize, and RPC call, and RPC is the preferred<\/strong> way to do this call. Clear back to Ken Henderson's original writings on RPC vs SQLBatch. That's why the APIs do it that way.\n<\/p>\n

\nWith SQLProfiler capturing Showplan XML Statistics Profile, RPC:Starting\/Completed, SQL:BatchStarting\/Completed, and all the cache events, I see (in this order) for the APIs:\n<\/p>\n

\nShowplan XML (for the 2 inserts)
\nRPC:Starting
\nSP:CacheInsert (for the proc)
\nShowplan XML (for the proc)
\nRPC:Completed\n<\/p>\n

\nEven subsequent execution generates a new plan for the inserts and a CacheHit for the proc.\n<\/p>\n

\nExecuting the same statements from SSMS:\n<\/p>\n

\nSP:CacheInsert (for the batch)
\nSQL:BatchStarting
\nShowplan XML (for the batch of 2 inserts)
\nShowplan XML (for the batch of 2 inserts)
\nSP:CacheInsert (for the proc)
\nShowplan XML (for the proc)
\nSQL:BatchCompleted\n<\/p>\n

\nNow, to look at how the statement is executed in ADO.NET. There are three possible ways:
\n1. Execute as a CommandType.StoredProcedure with a TVP param of SqlDbType.Structured
\n2. Execute as a CommandType.CommandText with the text "exec your_proc @p" with a TVP param of SqlDbType.Structured
\n3. Execute as a CommandType.CommandText with the text "declare @t your_table_type;insert @t values(1);insert @t values(2);exec your_proc @t" and no parameters associated with the SqlCommand object.\n<\/p>\n

\nCase 1 is submitted as an RPC call, no cache on the insert plan
\nCase 2 is translated into sp_executesql, submitted as an RPC call, no cache on the insert plan
\nCase 3 replicates SSMS behavior, submitted as SQLBatch, caching on the insert\/batch occurs\n<\/p>\n

\nMoreover there are three possible ways to produce the parameter for cases 1 and 2:
\n1. Use a DataTable
\n2. Use a DataReader
\n3. Use a collection (I used List<T>) of SqlDataRecord\n<\/p>\n

\nThere is NO difference in the cache behavior based on which way I produce the parameter.\n<\/p>\n

\nBTW, for the ODBC afficianados, I'm using SqlExecDirect "{CALL my_proc ?}" and parameters produced with SQLBindParameter and moving the parameter focus for the TVP parm.\n<\/p>\n

\nThat leaves use with the less-than-happy occurance that, to acheive query plan reuse of both plans, we must built up a SQL statement (case 3 above) in code. None of the cool SqlDbType.Structured ways produce query plan resue. Oh.\n<\/p>\n

\nSo, I got out my friendly TDS spec<\/a> to attempt to determine why. Although there are a lot of new packet types for TVP support, there's nothing that validates the behavior I'm seeing. If anyone knows for sure that this is expected behavior or that the Profiler, cache DMVs, and Perfmon are all being confused because of the new protocol packets and mis-reporting things, I'd prefer to hear that. Else, I'll be filing a connect bug soon.\n<\/p>\n

\nNote: I did file a bug and it was resolved as "by design". Read the reprise here<\/a>.\n<\/p>\n

\n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"

A couple of people reported problems with SQL Server 2008's table-valued parameters and it eventually got back to me. Had to try it out. The reported problem is that there's a plan compile each time TVPs are used from ADO.NET. Interestingly, when the same statements are executed from SSMS (which is an ADO.NET client program), […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,29],"tags":[],"class_list":["post-577","post","type-post","status-publish","format-standard","hentry","category-data-access","category-sql-server-2008"],"yoast_head":"\nThe interesting case of TVPs and plan compilation - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/the-interesting-case-of-tvps-and-plan-compilation\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The interesting case of TVPs and plan compilation - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"A couple of people reported problems with SQL Server 2008's table-valued parameters and it eventually got back to me. Had to try it out. The reported problem is that there's a plan compile each time TVPs are used from ADO.NET. Interestingly, when the same statements are executed from SSMS (which is an ADO.NET client program), […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/the-interesting-case-of-tvps-and-plan-compilation\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2010-12-23T13:49:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-01-20T20:19:32+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/the-interesting-case-of-tvps-and-plan-compilation\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/the-interesting-case-of-tvps-and-plan-compilation\/\",\"name\":\"The interesting case of TVPs and plan compilation - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2010-12-23T13:49:00+00:00\",\"dateModified\":\"2014-01-20T20:19:32+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/the-interesting-case-of-tvps-and-plan-compilation\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/the-interesting-case-of-tvps-and-plan-compilation\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/the-interesting-case-of-tvps-and-plan-compilation\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Data Access\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/data-access\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"The interesting case of TVPs and plan compilation\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"The interesting case of TVPs and plan compilation - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/the-interesting-case-of-tvps-and-plan-compilation\/","og_locale":"en_US","og_type":"article","og_title":"The interesting case of TVPs and plan compilation - Bob Beauchemin","og_description":"A couple of people reported problems with SQL Server 2008's table-valued parameters and it eventually got back to me. Had to try it out. The reported problem is that there's a plan compile each time TVPs are used from ADO.NET. Interestingly, when the same statements are executed from SSMS (which is an ADO.NET client program), […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/the-interesting-case-of-tvps-and-plan-compilation\/","og_site_name":"Bob Beauchemin","article_published_time":"2010-12-23T13:49:00+00:00","article_modified_time":"2014-01-20T20:19:32+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/the-interesting-case-of-tvps-and-plan-compilation\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/the-interesting-case-of-tvps-and-plan-compilation\/","name":"The interesting case of TVPs and plan compilation - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2010-12-23T13:49:00+00:00","dateModified":"2014-01-20T20:19:32+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/the-interesting-case-of-tvps-and-plan-compilation\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/the-interesting-case-of-tvps-and-plan-compilation\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/the-interesting-case-of-tvps-and-plan-compilation\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Data Access","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/data-access\/"},{"@type":"ListItem","position":3,"name":"The interesting case of TVPs and plan compilation"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/577","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=577"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/577\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=577"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=577"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=577"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}