(I got CTP 6 running on another machine, so I’m working except for parallel plans now since it’s only a single-proc. You guys will have to wait on that operator of the day article for now \ud83d\ude42<\/p>\n
Kudos to my former teammembers for getting filtered indexes into CTP6. It’s pretty darn neat, and I’ll show you a few tidbits that are interesting.<\/p>\n
So, filtered indexes are not really a new “feature” in some sense. You can do everything in a filtered index with an indexed view. Of course, indexed view matching is only supported in the Enterprise edition of the software, so perhaps not everyone has seen those benefits. <\/p>\n
Indexed views are a tricky feature – generalized tree pattern matching is hard (read: CPU expensive), and if you’ve looked in the BOL the list of restrictions is so long that reminds me of filling out tax forms. However, the other side effect that occurs when you have indexed views is that the optimizer has to go through additional phases of its search in order to apply them. The optimizer has a couple of buckets of rules it can run, and most plans find a nice plan in the first or second round of rules. Generalized indexed view matching is restricted to the last set of buckets, which usually means that there are a lot more rules that have to run before that view can be matched. The bottom line is that the compilation cost isn’t bad for a single query (usually), but it’s the sort of thing that can make the difference between an application that can be used in UI response-time requirements or not.<\/p>\n
Enter the filtered index. This is a recognition that there are a lot of indexed views that don’t need tons and tons of fancy equations, joins, etc. These are single-tabled views that have simple predicates. Once you get into this ballpark, you can bolt this on to the super-efficient index matching code and you can enable a whole new class of application from what you could build previously. This is why I am excited about this feature.<\/p>\n
I haven’t looked to see where this feature will fall into the SKU matrix yet, and I’m sure that they’re still pondering that very question. However, you guys should play with this on CTP6 – it’s nifty!<\/p>\n
So, first things first. Let’s build one of these guys and see if we can match it:<\/p>\n
create<\/span> database<\/span> t1\r\nuse<\/span> t1\r\ndrop<\/span> table<\/span> t1\r\ncreate<\/span> table<\/span> t1(col1 int<\/span>, col2 nchar<\/span>(2000), col3 time)\r\n\r\ncreate<\/span> index<\/span> i1 on<\/span> t1(col1) where<\/span> col1 > 5 and<\/span> col1 < 20\r\ndeclare<\/span> @p int<\/span> \r\nset<\/span> @p =0\r\nwhile<\/span> @p < 20000\r\nbegin<\/span>\r\ninsert<\/span> into<\/span> t1(col1) values<\/span> (rand<\/span>()*10000)\r\nset<\/span> @p=@p+1\r\nend<\/span>\r\n<\/span><\/pre>\nok so I’ve filled this table up with a lot of useless data and created a nice little filtered index.<\/p>\n
select<\/span> * from<\/span> t1 where<\/span> col1 > 5 and<\/span> col1 < 20<\/span><\/pre>\n |–Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1007]) OPTIMIZED WITH UNORDERED PREFETCH)
|–Compute Scalar(DEFINE:([Expr1006]=BmkToPage([Bmk1000])))
| |–Index Scan(OBJECT:([t1].[dbo].[t1].[i1]))
|–RID Lookup(OBJECT:([t1].[dbo].[t1]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)<\/p>\n
select<\/span> * from<\/span> t1 where<\/span> col1 > 5 and<\/span> col1 < 10<\/span><\/pre>\n |–Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]) OPTIMIZED)
|–Compute Scalar(DEFINE:([Expr1006]=BmkToPage([Bmk1000])))
| |–Index Seek(OBJECT:([t1].[dbo].[t1].[i1]), SEEK:([t1].[dbo].[t1].[col1] < (10)) ORDERED FORWARD)
|–RID Lookup(OBJECT:([t1].[dbo].[t1]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)<\/p>\n
so in the first example I’ve created a query against the table that directly matches the index condition. It matches and even generates an index scan (slightly faster since it doesn’t have to navigate down the b-tree to start returning rows).<\/p>\n
The second example is interesting because is a proper subset of the index. The indexed view matching code can do subsumption as well. It generates a seek in this case and returns only part of the filtered index.<\/p>\n
Both cases do RID lookups back to the heap since I did SELECT * and the index is not “covering”.<\/p>\n
The other data point I’ll leave you with this evening is that the showplan_xml has something interesting in it:<\/p>\n
StmtSimple<\/span> StatementText<\/span>=”<\/span>select * from t1 where col1 > 5 and col1 < 10<\/b>“<\/span> StatementId<\/span>=”<\/span>1<\/b>“<\/span>
\nStatementCompId<\/span>=”<\/span>1<\/b>“<\/span> StatementType<\/span>=”<\/span>SELECT<\/b>“<\/span> StatementSubTreeCost<\/span>=”<\/span>0.0474183<\/b>“<\/span>
\nStatementEstRows<\/span>=”<\/span>7<\/b>“<\/span> StatementOptmLevel<\/span>=”<\/span>FULL<\/b>“<\/span>
\nStatementOptmEarlyAbortReason<\/span>=”<\/span>GoodEnoughPlanFound<\/b>“<\/span>
\nParameterizedText<\/span>=”<\/span>(@1 tinyint,@2 tinyint)SELECT
\n* FROM [t1] WHERE [col1]>@1 AND [col1]<@2<\/b>“<\/span>><\/span><\/p>\nWell, there are two interesting things. First, StatementOptmLevel=FULL means that we didn’t get a trivial plan. There is a parameterized text field, but I don’t think that is being used here. these two queries are showing up as different plans in the plan cache (and obviously they have different plans). So, absent forced autoparam, I don’t think that trivial plan is working on these guys. This makes sense since there is a cost-based plan choice.<\/p>\n
That’s it for me tonight guys. Happy Querying!<\/p>\n
Conor<\/p>\n
PS: Thanks for all of the comments and posts trying to help me get back to a working state. I’ve tried many of the suggestions without luck, but I’m going to try playing with the orca tool, perhaps tomorrow or so, to see if I can get my main machine back up and working.<\/p>\n","protected":false},"excerpt":{"rendered":"
(I got CTP 6 running on another machine, so I’m working except for parallel plans now since it’s only a single-proc. You guys will have to wait on that operator of the day article for now \ud83d\ude42 Kudos to my former teammembers for getting filtered indexes into CTP6. It’s pretty darn neat, and I’ll show […]<\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-411","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\n
Filtered Indexes Make Me Drool... - Conor Cunningham<\/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\/conor\/filtered-indexes-make-me-drool\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Filtered Indexes Make Me Drool... - Conor Cunningham\" \/>\n<meta property=\"og:description\" content=\"(I got CTP 6 running on another machine, so I’m working except for parallel plans now since it’s only a single-proc. You guys will have to wait on that operator of the day article for now \ud83d\ude42 Kudos to my former teammembers for getting filtered indexes into CTP6. It’s pretty darn neat, and I’ll show […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/conor\/filtered-indexes-make-me-drool\/\" \/>\n<meta property=\"og:site_name\" content=\"Conor Cunningham\" \/>\n<meta property=\"article:published_time\" content=\"2008-02-25T21:35:45+00:00\" \/>\n<meta name=\"author\" content=\"Conor Cunningham\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Conor Cunningham\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/filtered-indexes-make-me-drool\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/filtered-indexes-make-me-drool\/\",\"name\":\"Filtered Indexes Make Me Drool... - Conor Cunningham\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#website\"},\"datePublished\":\"2008-02-25T21:35:45+00:00\",\"dateModified\":\"2008-02-25T21:35:45+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/filtered-indexes-make-me-drool\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/conor\/filtered-indexes-make-me-drool\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/filtered-indexes-make-me-drool\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Filtered Indexes Make Me Drool…\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/\",\"name\":\"Conor Cunningham\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3\",\"name\":\"Conor Cunningham\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g\",\"caption\":\"Conor Cunningham\"},\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/author\/conor\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Filtered Indexes Make Me Drool... - Conor Cunningham","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\/conor\/filtered-indexes-make-me-drool\/","og_locale":"en_US","og_type":"article","og_title":"Filtered Indexes Make Me Drool... - Conor Cunningham","og_description":"(I got CTP 6 running on another machine, so I’m working except for parallel plans now since it’s only a single-proc. You guys will have to wait on that operator of the day article for now \ud83d\ude42 Kudos to my former teammembers for getting filtered indexes into CTP6. It’s pretty darn neat, and I’ll show […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/conor\/filtered-indexes-make-me-drool\/","og_site_name":"Conor Cunningham","article_published_time":"2008-02-25T21:35:45+00:00","author":"Conor Cunningham","twitter_misc":{"Written by":"Conor Cunningham","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/filtered-indexes-make-me-drool\/","url":"https:\/\/www.sqlskills.com\/blogs\/conor\/filtered-indexes-make-me-drool\/","name":"Filtered Indexes Make Me Drool... - Conor Cunningham","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#website"},"datePublished":"2008-02-25T21:35:45+00:00","dateModified":"2008-02-25T21:35:45+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/filtered-indexes-make-me-drool\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/conor\/filtered-indexes-make-me-drool\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/filtered-indexes-make-me-drool\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/conor\/"},{"@type":"ListItem","position":2,"name":"Filtered Indexes Make Me Drool…"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/conor\/","name":"Conor Cunningham","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/conor\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3","name":"Conor Cunningham","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g","caption":"Conor Cunningham"},"url":"https:\/\/www.sqlskills.com\/blogs\/conor\/author\/conor\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts\/411","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/comments?post=411"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts\/411\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/media?parent=411"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/categories?post=411"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/tags?post=411"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}