{"id":944,"date":"2013-03-11T09:59:41","date_gmt":"2013-03-11T16:59:41","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/joe\/?p=944"},"modified":"2013-03-11T10:19:22","modified_gmt":"2013-03-11T17:19:22","slug":"are-filtered-statistics-in-unmatched-scenarios","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/are-filtered-statistics-in-unmatched-scenarios\/","title":{"rendered":"Are Filtered Statistics Useful with Parameterization?"},"content":{"rendered":"<p>This post is based on a question I received last week\u2026<\/p>\n<p>If we have a filtered index that was not a match for a query due to parameterization (or a variation of which the parameter is not known at compile time), we can see the unmatched filtered index name in the UnmatchedIndexes element of SHOWPLAN_XML output.<\/p>\n<p>The question I received on this subject was, \u201cwhat about for filtered statistics?\u201d\u00a0 Can those still be used for parameterized scenarios? The following is a quick test to see if the stats also don\u2019t qualify for matching\u2026 I\u2019ll start by showing the filtered statistics matching behavior using the following statistics object:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nUSE &#x5B;Credit];\r\nGO\r\n\r\nCREATE STATISTICS fstat_charge_provider_no\r\nON &#x5B;dbo].&#x5B;charge] (&#x5B;member_no], &#x5B;category_no])\r\nWHERE &#x5B;provider_no] = 484;\r\nGO\r\n<\/pre>\n<p>The following query will use the statistics, and I\u2019ve included the trace flags to show the usage from a query optimizer perspective:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT DISTINCT\r\n        &#x5B;c].&#x5B;member_no],\r\n        &#x5B;c].&#x5B;category_no],\r\n        &#x5B;c].&#x5B;provider_no]\r\nFROM    &#x5B;dbo].&#x5B;charge] AS c\r\nWHERE   &#x5B;c].&#x5B;provider_no] = 484 AND\r\n        &#x5B;c].&#x5B;member_no] = 9527 AND\r\n        &#x5B;c].&#x5B;category_no] = 2\r\nOPTION  (QUERYTRACEON 3604, QUERYTRACEON 9204);\r\nGO\r\n<\/pre>\n<p>This returns the following message output based on an initial compilation:<\/p>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nStats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 2, ColumnName: category_no, EmptyTable: FALSE\r\n\r\nStats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 3, ColumnName: provider_no, EmptyTable: FALSE\r\n\r\nStats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 6, ColumnName: member_no, EmptyTable: FALSE\r\n\r\nFiltered stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 7, ColumnName: member_no, \r\nExpr: (&#x5B;provider_no]=(484)), EmptyTable: FALSE\r\n\r\nFiltered stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 7, ColumnName: member_no, \r\nExpr: (&#x5B;provider_no]=(484)), EmptyTable: FALSE\r\n\r\nFiltered stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 7, ColumnName: member_no, \r\nExpr: (&#x5B;provider_no]=(484)), EmptyTable: FALSE\r\n<\/pre>\n<p>Now let\u2019s use a local variable instead for the provider_no predicate (used for the filtered statistics):<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDECLARE @provider_no INT = 484;\r\n\r\nSELECT DISTINCT\r\n        &#x5B;c].&#x5B;member_no],\r\n        &#x5B;c].&#x5B;category_no],\r\n        &#x5B;c].&#x5B;provider_no]\r\nFROM    &#x5B;dbo].&#x5B;charge] AS c\r\nWHERE   &#x5B;c].&#x5B;provider_no] = @provider_no AND\r\n        &#x5B;c].&#x5B;member_no] = 9527 AND\r\n        &#x5B;c].&#x5B;category_no] = 2\r\nOPTION  (QUERYTRACEON 3604, QUERYTRACEON 9204);\r\nGO\r\n<\/pre>\n<p>This time we see the following trace flag 9204 output:<\/p>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nStats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 2, ColumnName: category_no, EmptyTable: FALSE\r\n\r\nStats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 3, ColumnName: provider_no, EmptyTable: FALSE\r\n\r\nStats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 6, ColumnName: member_no, EmptyTable: FALSE\r\n<\/pre>\n<p>Even though the provider_no value matched \u201c484\u201d, the filtered stats were not loaded as the value was not known at compile time. This is one specific scenario \u2013 so if you have a different one on this subject, please feel free to share on the comments of this post.<\/p>\n<p>Thanks!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This post is based on a question I received last week\u2026 If we have a filtered index that was not a match for a query due to parameterization (or a variation of which the parameter is not known at compile time), we can see the unmatched filtered index name in the UnmatchedIndexes element of SHOWPLAN_XML [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[],"class_list":["post-944","post","type-post","status-publish","format-standard","hentry","category-performance"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Are Filtered Statistics Useful with Parameterization? - 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\/are-filtered-statistics-in-unmatched-scenarios\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Are Filtered Statistics Useful with Parameterization? - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"This post is based on a question I received last week\u2026 If we have a filtered index that was not a match for a query due to parameterization (or a variation of which the parameter is not known at compile time), we can see the unmatched filtered index name in the UnmatchedIndexes element of SHOWPLAN_XML [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/are-filtered-statistics-in-unmatched-scenarios\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2013-03-11T16:59:41+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-03-11T17:19:22+00:00\" \/>\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=\"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\/joe\/are-filtered-statistics-in-unmatched-scenarios\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/are-filtered-statistics-in-unmatched-scenarios\/\",\"name\":\"Are Filtered Statistics Useful with Parameterization? - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2013-03-11T16:59:41+00:00\",\"dateModified\":\"2013-03-11T17:19:22+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/are-filtered-statistics-in-unmatched-scenarios\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/are-filtered-statistics-in-unmatched-scenarios\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/are-filtered-statistics-in-unmatched-scenarios\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Performance\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/performance\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Are Filtered Statistics Useful with Parameterization?\"}]},{\"@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":"Are Filtered Statistics Useful with Parameterization? - 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\/are-filtered-statistics-in-unmatched-scenarios\/","og_locale":"en_US","og_type":"article","og_title":"Are Filtered Statistics Useful with Parameterization? - Joe Sack","og_description":"This post is based on a question I received last week\u2026 If we have a filtered index that was not a match for a query due to parameterization (or a variation of which the parameter is not known at compile time), we can see the unmatched filtered index name in the UnmatchedIndexes element of SHOWPLAN_XML [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/are-filtered-statistics-in-unmatched-scenarios\/","og_site_name":"Joe Sack","article_published_time":"2013-03-11T16:59:41+00:00","article_modified_time":"2013-03-11T17:19:22+00:00","author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/are-filtered-statistics-in-unmatched-scenarios\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/are-filtered-statistics-in-unmatched-scenarios\/","name":"Are Filtered Statistics Useful with Parameterization? - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2013-03-11T16:59:41+00:00","dateModified":"2013-03-11T17:19:22+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/are-filtered-statistics-in-unmatched-scenarios\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/are-filtered-statistics-in-unmatched-scenarios\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/are-filtered-statistics-in-unmatched-scenarios\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Performance","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/performance\/"},{"@type":"ListItem","position":3,"name":"Are Filtered Statistics Useful with Parameterization?"}]},{"@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\/944","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=944"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/944\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=944"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=944"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=944"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}