{"id":410,"date":"2008-03-02T15:05:32","date_gmt":"2008-03-02T15:05:32","guid":{"rendered":"\/blogs\/conor\/post\/SQL-2008-CTP6-Filtered-Indexes-in-Action-disjunction-of-ranges-and-index-choice.aspx"},"modified":"2008-03-02T15:05:32","modified_gmt":"2008-03-02T15:05:32","slug":"sql-2008-ctp6-filtered-indexes-in-action-disjunction-of-ranges-and-index-choice","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/conor\/sql-2008-ctp6-filtered-indexes-in-action-disjunction-of-ranges-and-index-choice\/","title":{"rendered":"SQL 2008 CTP6 Filtered Indexes in Action – disjunction of ranges and index choice"},"content":{"rendered":"

After fighting off a cold all week, I’ve had some more time to go play with CTP6 on my secondary machine.<\/p>\n

Today we’ll do some experiments to see how the Optimizer picks plans for filtered indexes.  This will help you figure out how to build your database schemas and queries to take advantage of this new kind of index.<\/p>\n

I was involved in the inception of the idea to build filtered indexes into SQL Server, but I didn’t have anything to do with the implementation.  So, what I’ll cover today is just me playing with a feature to see what it can do.  <\/p>\n

I thought that it would be good to play with disjoint ranges and see whether they can be “covered” by a filtered index.  In my last blog post, I covered how a basic, single range case *is* covered by a filtered index when the predicates match up correctly.  In this post, I’ll try multiple ranges to see what happens.<\/p>\n

Same database setup as before (just a table with wide rows and enough rows to make index choice obvious to the optimizer):<\/p>\n

use<\/span> t1

create<\/span> database<\/span> t1
use<\/span> t1
drop<\/span> table<\/span> t1
create<\/span> table<\/span> t1(col1 int<\/span>, col2 nchar<\/span>(2000), col3 time)

create<\/span> index<\/span> i1 on<\/span> t1(col1) where<\/span> col1 > 5 and<\/span> col1 < 20
<\/span>


create<\/span> index<\/span> i1 on<\/span> t1(col1) where<\/span> col1 > 5 and<\/span> col1 < 20
declare<\/span> @p int<\/span> 
set<\/span> @p =0
while<\/span> @p < 20000
begin<\/span>
insert<\/span> into<\/span> t1(col1) values<\/span> (rand<\/span>()*10000)
set<\/span> @p=@p+1
end
<\/span><\/span>create<\/span> index<\/span> i2 on<\/span> t1(col1) where<\/span> col1 > 25 and<\/span> col1 < 40<\/span>


OK I added a second index over a different range in the same column.  This is not a case that the 
SQL Server 2005 optimizer had to handle.

query 1: a query with a disjunction (OR) of ranges (BETWEEN\/AND).
select<\/span> col1 from<\/span> t1 where<\/span> (col1 > 5 and<\/span> col1 < 20) or<\/span> (col1 > 25 and<\/span> col1 < 40)<\/span><\/pre>\n
  |--Table Scan(OBJECT:([t1].[dbo].[t1]), WHERE:([t1].[dbo].[t1].[col1]>(5) AND [t1].[dbo].[t1].[col1]<(20) OR [t1].[dbo].[t1].[col1]>(25) AND [t1].[dbo].[t1].[col1]<(40)))<\/p>\n
Hrm.  Well, there doesn't seem to be code in CTP that does index unions for cases like this, unfortunately.
Hinting isn't an option either because the index hint code returns an error if the hinted index does not
completely cover the index.  On my machine, I get:<\/p>\n
(43 row(s) affected)<\/p>\n
 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 2661 ms.<\/p>\n
That elapsed time is mostly reading pages off my IDE drive.  To be clear, this is the _cold cache_ time, 
which means that I ran \"DBCC dropcleanbuffers\" before my run.  This throws out all of the buffer pool 
pages and forces a read from disk.  One assumption in the costing model for the SQL Server QP is that the
pages are _not_ in memory already (and there are cases where this becomes interesting).  For this example,
it just means that we have a big table scan with lots of IOs to do (and is therefore slow).<\/p>\n
Second example: IN lists<\/p>\n
select<\/span> col1 from<\/span> t1 where<\/span> col1 in<\/span> (6, 7, 8, 26)<\/span><\/pre>\n
  |--Table Scan(OBJECT:([t1].[dbo].[t1]), WHERE:([t1].[dbo].[t1].[col1]=(6) OR [t1].[dbo].[t1].[col1]=(7) OR [t1].[dbo].[t1].[col1]=(8) OR [t1].[dbo].[t1].[col1]=(26)))<\/p>\n
Well, this is consistent.  IN is generally just a list of ORs as far as the QP is concerned.  Runtime for this query should be very similar to what we saw in our last example:<\/p>\n
(7 row(s) affected)<\/p>\n
 SQL Server Execution Times:
   CPU time = 63 ms,  elapsed time = 2708 ms.<\/p>\n
OK, so what’s an enterprising SQL developer to do?  Well, you can rewrite the query as a UNION ALL as long as you know that the ranges are disjoint and cover the predicate properly.<\/p>\n
select<\/span> col1 from<\/span> t1 with<\/span> (index<\/span>=i1) where<\/span> (col1 > 5 and<\/span> col1 < 20) \r\nunion<\/span> all<\/span> \r\nselect<\/span> col1 from<\/span> t1 with<\/span> (index<\/span>=i2) where<\/span> (col1 > 25 and<\/span> col1 < 40)\r\n<\/span><\/pre>\n
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.<\/p>\n
(43 row(s) affected)<\/p>\n
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.<\/p>\n
That’s a lot nicer.<\/p>\n
I have no idea if this is something that they intend to add for this release of the product.  I’m not really even sure how common this is.  How often do you want to apply a disjunction of ranges to multiple partial indexes where you don’t want to create an index over the whole table?  No clue.  If you have such an example, I’d love to hear about it – drop me a line at conor@sqlskills.com<\/p>\n
In the meantime, this is just a post on something interesting I learned while playing with the new CTP6 for SQL Server 2008 that I thought you might like to know.<\/p>\n
Happy Querying!<\/p>\n
Conor Cunningham<\/p>\n","protected":false},"excerpt":{"rendered":"
After fighting off a cold all week, I’ve had some more time to go play with CTP6 on my secondary machine. Today we’ll do some experiments to see how the Optimizer picks plans for filtered indexes.  This will help you figure out how to build your database schemas and queries to take advantage of this […]<\/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-410","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\nSQL 2008 CTP6 Filtered Indexes in Action - disjunction of ranges and index choice - 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\/sql-2008-ctp6-filtered-indexes-in-action-disjunction-of-ranges-and-index-choice\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL 2008 CTP6 Filtered Indexes in Action - disjunction of ranges and index choice - Conor Cunningham\" \/>\n<meta property=\"og:description\" content=\"After fighting off a cold all week, I’ve had some more time to go play with CTP6 on my secondary machine. Today we’ll do some experiments to see how the Optimizer picks plans for filtered indexes.  This will help you figure out how to build your database schemas and queries to take advantage of this […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/conor\/sql-2008-ctp6-filtered-indexes-in-action-disjunction-of-ranges-and-index-choice\/\" \/>\n<meta property=\"og:site_name\" content=\"Conor Cunningham\" \/>\n<meta property=\"article:published_time\" content=\"2008-03-02T15:05:32+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=\"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\/conor\/sql-2008-ctp6-filtered-indexes-in-action-disjunction-of-ranges-and-index-choice\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/sql-2008-ctp6-filtered-indexes-in-action-disjunction-of-ranges-and-index-choice\/\",\"name\":\"SQL 2008 CTP6 Filtered Indexes in Action - disjunction of ranges and index choice - Conor Cunningham\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#website\"},\"datePublished\":\"2008-03-02T15:05:32+00:00\",\"dateModified\":\"2008-03-02T15:05:32+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/sql-2008-ctp6-filtered-indexes-in-action-disjunction-of-ranges-and-index-choice\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/conor\/sql-2008-ctp6-filtered-indexes-in-action-disjunction-of-ranges-and-index-choice\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/sql-2008-ctp6-filtered-indexes-in-action-disjunction-of-ranges-and-index-choice\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL 2008 CTP6 Filtered Indexes in Action – disjunction of ranges and index choice\"}]},{\"@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":"SQL 2008 CTP6 Filtered Indexes in Action - disjunction of ranges and index choice - 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\/sql-2008-ctp6-filtered-indexes-in-action-disjunction-of-ranges-and-index-choice\/","og_locale":"en_US","og_type":"article","og_title":"SQL 2008 CTP6 Filtered Indexes in Action - disjunction of ranges and index choice - Conor Cunningham","og_description":"After fighting off a cold all week, I’ve had some more time to go play with CTP6 on my secondary machine. Today we’ll do some experiments to see how the Optimizer picks plans for filtered indexes.  This will help you figure out how to build your database schemas and queries to take advantage of this […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/conor\/sql-2008-ctp6-filtered-indexes-in-action-disjunction-of-ranges-and-index-choice\/","og_site_name":"Conor Cunningham","article_published_time":"2008-03-02T15:05:32+00:00","author":"Conor Cunningham","twitter_misc":{"Written by":"Conor Cunningham","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/sql-2008-ctp6-filtered-indexes-in-action-disjunction-of-ranges-and-index-choice\/","url":"https:\/\/www.sqlskills.com\/blogs\/conor\/sql-2008-ctp6-filtered-indexes-in-action-disjunction-of-ranges-and-index-choice\/","name":"SQL 2008 CTP6 Filtered Indexes in Action - disjunction of ranges and index choice - Conor Cunningham","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#website"},"datePublished":"2008-03-02T15:05:32+00:00","dateModified":"2008-03-02T15:05:32+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/sql-2008-ctp6-filtered-indexes-in-action-disjunction-of-ranges-and-index-choice\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/conor\/sql-2008-ctp6-filtered-indexes-in-action-disjunction-of-ranges-and-index-choice\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/sql-2008-ctp6-filtered-indexes-in-action-disjunction-of-ranges-and-index-choice\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/conor\/"},{"@type":"ListItem","position":2,"name":"SQL 2008 CTP6 Filtered Indexes in Action – disjunction of ranges and index choice"}]},{"@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\/410","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=410"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts\/410\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/media?parent=410"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/categories?post=410"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/tags?post=410"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}