{"id":1172,"date":"2014-03-20T09:21:12","date_gmt":"2014-03-20T16:21:12","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/joe\/?p=1172"},"modified":"2014-03-20T09:26:57","modified_gmt":"2014-03-20T16:26:57","slug":"mstvf-fixed-cardinality-value-sql-server-2014","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/mstvf-fixed-cardinality-value-sql-server-2014\/","title":{"rendered":"MSTVF Fixed Cardinality Value in SQL Server 2014"},"content":{"rendered":"<p>In SQL Server 2014 CTP2 in the AdventureWorks2012 database, execute the following batch:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\"> \r\nUSE &#x5B;AdventureWorks2012];\r\nGO\r\n\r\nSELECT &#x5B;PersonID], &#x5B;FirstName], &#x5B;LastName], &#x5B;JobTitle], &#x5B;BusinessEntityType]\r\nFROM &#x5B;dbo].&#x5B;ufnGetContactInformation](2)\r\nOPTION (QUERYTRACEON 9481);\u00a0 -- Legacy CE\r\n\r\nSELECT &#x5B;PersonID], &#x5B;FirstName], &#x5B;LastName], &#x5B;JobTitle], &#x5B;BusinessEntityType]\r\nFROM &#x5B;dbo].&#x5B;ufnGetContactInformation](2)\r\nOPTION (QUERYTRACEON 2312); -- New CE\r\n<\/pre>\n<p>The first query uses the legacy cardinality estimator and the second query uses the new cardinality estimator.\u00a0\u00a0 Both queries are referencing a multi-statement table valued function.<\/p>\n<p>Looking at the plan tree view in SQL Sentry Plan Explorer for the legacy CE plan, you\u2019ll see the following (estimating 1 row for the function operators):<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2014\/03\/image.png\"><img fetchpriority=\"high\" decoding=\"async\" style=\"display: inline; border: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2014\/03\/image_thumb.png\" width=\"737\" height=\"102\" border=\"0\" \/><\/a><\/p>\n<p>Looking at the new CE version of the plan tree, we see the following (estimating <em>100 rows<\/em> for the function operators):<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2014\/03\/image1.png\"><img decoding=\"async\" style=\"display: inline; border: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2014\/03\/image_thumb1.png\" width=\"746\" height=\"113\" border=\"0\" \/><\/a><\/p>\n<p>SQL Server 2014 uses a new default fixed cardinality for multi-statement table valued functions.<\/p>\n<p>A few thoughts:<\/p>\n<ul>\n<li>Whether 1 row or 100 rows, we\u2019re still using a fixed guess that may or may not reflect reality<\/li>\n<li>I\u2019m very wary of using MSTVFs in scenarios where the estimate is critical for plan quality (and oftentimes it is)<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>In SQL Server 2014 CTP2 in the AdventureWorks2012 database, execute the following batch: USE &#x5B;AdventureWorks2012]; GO SELECT &#x5B;PersonID], &#x5B;FirstName], &#x5B;LastName], &#x5B;JobTitle], &#x5B;BusinessEntityType] FROM &#x5B;dbo].&#x5B;ufnGetContactInformation](2) OPTION (QUERYTRACEON 9481);\u00a0 &#8212; Legacy CE SELECT &#x5B;PersonID], &#x5B;FirstName], &#x5B;LastName], &#x5B;JobTitle], &#x5B;BusinessEntityType] FROM &#x5B;dbo].&#x5B;ufnGetContactInformation](2) OPTION (QUERYTRACEON 2312); &#8212; New CE The first query uses the legacy cardinality estimator and the second [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42],"tags":[],"class_list":["post-1172","post","type-post","status-publish","format-standard","hentry","category-cardinality-estimation"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>MSTVF Fixed Cardinality Value in SQL Server 2014 - Joe Sack<\/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\/joe\/mstvf-fixed-cardinality-value-sql-server-2014\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MSTVF Fixed Cardinality Value in SQL Server 2014 - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"In SQL Server 2014 CTP2 in the AdventureWorks2012 database, execute the following batch: USE &#x5B;AdventureWorks2012]; GO SELECT &#x5B;PersonID], &#x5B;FirstName], &#x5B;LastName], &#x5B;JobTitle], &#x5B;BusinessEntityType] FROM &#x5B;dbo].&#x5B;ufnGetContactInformation](2) OPTION (QUERYTRACEON 9481);\u00a0 -- Legacy CE SELECT &#x5B;PersonID], &#x5B;FirstName], &#x5B;LastName], &#x5B;JobTitle], &#x5B;BusinessEntityType] FROM &#x5B;dbo].&#x5B;ufnGetContactInformation](2) OPTION (QUERYTRACEON 2312); -- New CE The first query uses the legacy cardinality estimator and the second [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/mstvf-fixed-cardinality-value-sql-server-2014\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2014-03-20T16:21:12+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-03-20T16:26:57+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2014\/03\/image_thumb.png\" \/>\n<meta name=\"author\" content=\"Joseph Sack\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joseph Sack\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/mstvf-fixed-cardinality-value-sql-server-2014\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/mstvf-fixed-cardinality-value-sql-server-2014\/\",\"name\":\"MSTVF Fixed Cardinality Value in SQL Server 2014 - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2014-03-20T16:21:12+00:00\",\"dateModified\":\"2014-03-20T16:26:57+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/mstvf-fixed-cardinality-value-sql-server-2014\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/mstvf-fixed-cardinality-value-sql-server-2014\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/mstvf-fixed-cardinality-value-sql-server-2014\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Cardinality Estimation\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/cardinality-estimation\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"MSTVF Fixed Cardinality Value in SQL Server 2014\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\",\"name\":\"Joe Sack\",\"description\":\"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\",\"name\":\"Joseph Sack\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"caption\":\"Joseph Sack\"},\"description\":\"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.\",\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/joe\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MSTVF Fixed Cardinality Value in SQL Server 2014 - Joe Sack","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\/joe\/mstvf-fixed-cardinality-value-sql-server-2014\/","og_locale":"en_US","og_type":"article","og_title":"MSTVF Fixed Cardinality Value in SQL Server 2014 - Joe Sack","og_description":"In SQL Server 2014 CTP2 in the AdventureWorks2012 database, execute the following batch: USE &#x5B;AdventureWorks2012]; GO SELECT &#x5B;PersonID], &#x5B;FirstName], &#x5B;LastName], &#x5B;JobTitle], &#x5B;BusinessEntityType] FROM &#x5B;dbo].&#x5B;ufnGetContactInformation](2) OPTION (QUERYTRACEON 9481);\u00a0 -- Legacy CE SELECT &#x5B;PersonID], &#x5B;FirstName], &#x5B;LastName], &#x5B;JobTitle], &#x5B;BusinessEntityType] FROM &#x5B;dbo].&#x5B;ufnGetContactInformation](2) OPTION (QUERYTRACEON 2312); -- New CE The first query uses the legacy cardinality estimator and the second [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/mstvf-fixed-cardinality-value-sql-server-2014\/","og_site_name":"Joe Sack","article_published_time":"2014-03-20T16:21:12+00:00","article_modified_time":"2014-03-20T16:26:57+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2014\/03\/image_thumb.png"}],"author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/mstvf-fixed-cardinality-value-sql-server-2014\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/mstvf-fixed-cardinality-value-sql-server-2014\/","name":"MSTVF Fixed Cardinality Value in SQL Server 2014 - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2014-03-20T16:21:12+00:00","dateModified":"2014-03-20T16:26:57+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/mstvf-fixed-cardinality-value-sql-server-2014\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/mstvf-fixed-cardinality-value-sql-server-2014\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/mstvf-fixed-cardinality-value-sql-server-2014\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Cardinality Estimation","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/cardinality-estimation\/"},{"@type":"ListItem","position":3,"name":"MSTVF Fixed Cardinality Value in SQL Server 2014"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/","name":"Joe Sack","description":"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648","name":"Joseph Sack","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","caption":"Joseph Sack"},"description":"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.","sameAs":["http:\/\/3.209.169.194\/blogs\/joe","https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack"],"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/1172","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/comments?post=1172"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/1172\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=1172"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=1172"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=1172"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}