{"id":560,"date":"2011-03-13T04:53:00","date_gmt":"2011-03-13T04:53:00","guid":{"rendered":"\/blogs\/bobb\/post\/TVPs-and-plan-compilation-the-reprise.aspx"},"modified":"2013-01-03T23:59:33","modified_gmt":"2013-01-04T07:59:33","slug":"tvps-and-plan-compilation-the-reprise","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/tvps-and-plan-compilation-the-reprise\/","title":{"rendered":"TVPs and plan compilation &#8211; the reprise"},"content":{"rendered":"<p>\nA few months ago, I published a blog entry entitled &quot;<a href=\"http:\/\/3.209.169.194\/blogs\/bobb\/the-interesting-case-of-tvps-and-plan-compilation\/\">The interesting case of TVPs and plan compilation<\/a>&quot; about the fact that a plan compile is reported (by perfmon) each time you correctly use a TVP from a program in RPC mode. After waiting a while,&nbsp;I published a connect bug.\n<\/p>\n<p>\nTurns out that this behavior is by design. Here&#39;s the response from the bug report:\n<\/p>\n<p>\n&quot;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.&quot;\n<\/p>\n<p>\nSo it IS by design and the behavior of a TVP, properly used in an ADO.NET program as SqlDbType.Structured (its a bit code different in OLE DB and ODBC but same result) is similar to bulk insert. Nice. And the compilation overhead is minimal (it is reported as a trivial plan in the plan XML) and (most likely) the speed of the special codepath more than makes up for the compile.\n<\/p>\n<p>\nMy only worry is that automated reporting tools or DBA scripts that watch perfmon for plan compiles&nbsp;may not understand what they are seeing. And it may be reported on forums&nbsp;that &quot;TVP use&nbsp;causes excessive recompilations&quot; (as it already has been). If you see such reports, point them at this blog post or the associated <a href=\"https:\/\/connect.microsoft.com\/SQLServer\/SearchResults.aspx?SearchQuery=648637\">connect item<\/a>.\n<\/p>\n<p>\n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A few months ago, I published a blog entry entitled &quot;The interesting case of TVPs and plan compilation&quot; about the fact that a plan compile is reported (by perfmon) each time you correctly use a TVP from a program in RPC mode. After waiting a while,&nbsp;I published a connect bug. Turns out that this behavior [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"class_list":["post-560","post","type-post","status-publish","format-standard","hentry","category-data-access"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>TVPs and plan compilation - the reprise - 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\/tvps-and-plan-compilation-the-reprise\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"TVPs and plan compilation - the reprise - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"A few months ago, I published a blog entry entitled &quot;The interesting case of TVPs and plan compilation&quot; about the fact that a plan compile is reported (by perfmon) each time you correctly use a TVP from a program in RPC mode. After waiting a while,&nbsp;I published a connect bug. Turns out that this behavior [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/tvps-and-plan-compilation-the-reprise\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2011-03-13T04:53:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T07:59:33+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=\"2 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\/tvps-and-plan-compilation-the-reprise\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/tvps-and-plan-compilation-the-reprise\/\",\"name\":\"TVPs and plan compilation - the reprise - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2011-03-13T04:53:00+00:00\",\"dateModified\":\"2013-01-04T07:59:33+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/tvps-and-plan-compilation-the-reprise\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/tvps-and-plan-compilation-the-reprise\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/tvps-and-plan-compilation-the-reprise\/#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\":\"TVPs and plan compilation &#8211; the reprise\"}]},{\"@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":"TVPs and plan compilation - the reprise - 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\/tvps-and-plan-compilation-the-reprise\/","og_locale":"en_US","og_type":"article","og_title":"TVPs and plan compilation - the reprise - Bob Beauchemin","og_description":"A few months ago, I published a blog entry entitled &quot;The interesting case of TVPs and plan compilation&quot; about the fact that a plan compile is reported (by perfmon) each time you correctly use a TVP from a program in RPC mode. After waiting a while,&nbsp;I published a connect bug. Turns out that this behavior [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/tvps-and-plan-compilation-the-reprise\/","og_site_name":"Bob Beauchemin","article_published_time":"2011-03-13T04:53:00+00:00","article_modified_time":"2013-01-04T07:59:33+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/tvps-and-plan-compilation-the-reprise\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/tvps-and-plan-compilation-the-reprise\/","name":"TVPs and plan compilation - the reprise - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2011-03-13T04:53:00+00:00","dateModified":"2013-01-04T07:59:33+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/tvps-and-plan-compilation-the-reprise\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/tvps-and-plan-compilation-the-reprise\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/tvps-and-plan-compilation-the-reprise\/#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":"TVPs and plan compilation &#8211; the reprise"}]},{"@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\/560","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=560"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/560\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=560"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=560"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=560"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}