{"id":651,"date":"2009-03-07T19:29:00","date_gmt":"2009-03-07T19:29:00","guid":{"rendered":"\/blogs\/bobb\/post\/Spatial-Indexes-in-SP1-almost-no-hints-required.aspx"},"modified":"2013-01-03T23:59:46","modified_gmt":"2013-01-04T07:59:46","slug":"spatial-indexes-in-sp1-almost-no-hints-required","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-in-sp1-almost-no-hints-required\/","title":{"rendered":"Spatial Indexes in SP1, almost no hints required"},"content":{"rendered":"<p>\nOn experimenting with the CTP of SQL Server 2008 SP1, I found that there were some changes made to the costing algorithm, vis-a-vis spatial indexes. In the RTM version, spatial indexes weren&#39;t uses as often as they could be, because the query costing was too high compared to the spatial filters. This made programmers resort to index hints for spatial indexes at the slightest provication. Here&#39;s some representative examples:\n<\/p>\n<p>\n&#8212; find surrounding zipcodes<br \/>\n&#8212; no indexes used, 5 secs<br \/>\n&#8212; cost pre-SP1: 45.2741<br \/>\n&#8212; index used in SP1, subsecond<br \/>\n&#8212; cost SP1: 14.2199<br \/>\ndeclare @id varchar(9) = &#39;97225&#39;<br \/>\nexec sp_executesql<br \/>\nN&#39;select b.id, b.shape.STAsText() <br \/>\nfrom zipcodes a, zipcodes b <br \/>\nwhere a.id = @id and a.shape.STIntersects(b.shape)=1&#39;, <a href=\"mailto:N'@id\">N&#39;@id<\/a> varchar(9)&#39;, @id<br \/>\ngo\n<\/p>\n<p>\n&#8212; cost SP1: 26.3341<br \/>\n&#8212; cost pre-SP1: 219.754<br \/>\ndeclare @id varchar(9) = &#39;162243&#39;<br \/>\nexec sp_executesql<br \/>\nN&#39;select b.id, b.shape.STAsText() <br \/>\nfrom census a, census b <br \/>\nwhere a.id = @id and a.shape.STIntersects(b.shape)=1&#39;, <a href=\"mailto:N'@id\">N&#39;@id<\/a> varchar(9)&#39;, @id<br \/>\ngo\n<\/p>\n<p>\nSo you should not have to hint spatial indexes as frequently (or at all) in SP1. If you&#39;d had those hints encapsulated in plan guides, you can just turn the plan guides off.\n<\/p>\n<p>\nI&#39;m still having trouble with some queries picking the best spatial index out of multiple choices; an &quot;hhhh&quot; index vs a &quot;mmmm&quot; index for example. So you may still have to hint if you have multiple spatial indexes for varied use cases. But for a single use-case\/single index, it appears to pick the best of &quot;use spatial index&quot; vs &quot;scan and filter&quot; every time. To determine which is the best spatial index for a specific use-case have a look at the spatial analysis proc series starting <a href=\"http:\/\/3.209.169.194\/blogs\/bobb\/using-the-spatial-index-diagnostic-stored-procedures\/\" class=\"broken_link\">a few postings back<\/a>.\n<\/p>\n<p>\nAlso, don&#39;t forget that in order to give the plan coster the best information, always use stored procedure parameters or parameterized queries. If you&#39;re just executing T-SQL batches in SSMS, sp_executesql will do the trick; if you use parameterized queries in any of the database APIs (e.g. ADO.NET), they&#39;ll convert it to a call to sp_executesql for you. And don&#39;t forget to use the right syntax for the spatial index method, that is, use &quot;a.STIntersects(b) = 1&quot; rather than &quot;1 = a.STIntersects(b)&quot;.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>On experimenting with the CTP of SQL Server 2008 SP1, I found that there were some changes made to the costing algorithm, vis-a-vis spatial indexes. In the RTM version, spatial indexes weren&#39;t uses as often as they could be, because the query costing was too high compared to the spatial filters. This made programmers resort [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29,36],"tags":[],"class_list":["post-651","post","type-post","status-publish","format-standard","hentry","category-sql-server-2008","category-sql-server-spatial"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Spatial Indexes in SP1, almost no hints required - 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\/spatial-indexes-in-sp1-almost-no-hints-required\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Spatial Indexes in SP1, almost no hints required - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"On experimenting with the CTP of SQL Server 2008 SP1, I found that there were some changes made to the costing algorithm, vis-a-vis spatial indexes. In the RTM version, spatial indexes weren&#039;t uses as often as they could be, because the query costing was too high compared to the spatial filters. This made programmers resort [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-in-sp1-almost-no-hints-required\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2009-03-07T19:29:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T07:59:46+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\/spatial-indexes-in-sp1-almost-no-hints-required\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-in-sp1-almost-no-hints-required\/\",\"name\":\"Spatial Indexes in SP1, almost no hints required - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2009-03-07T19:29:00+00:00\",\"dateModified\":\"2013-01-04T07:59:46+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-in-sp1-almost-no-hints-required\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-in-sp1-almost-no-hints-required\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-in-sp1-almost-no-hints-required\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2008\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2008\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Spatial Indexes in SP1, almost no hints required\"}]},{\"@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":"Spatial Indexes in SP1, almost no hints required - 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\/spatial-indexes-in-sp1-almost-no-hints-required\/","og_locale":"en_US","og_type":"article","og_title":"Spatial Indexes in SP1, almost no hints required - Bob Beauchemin","og_description":"On experimenting with the CTP of SQL Server 2008 SP1, I found that there were some changes made to the costing algorithm, vis-a-vis spatial indexes. In the RTM version, spatial indexes weren&#39;t uses as often as they could be, because the query costing was too high compared to the spatial filters. This made programmers resort [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-in-sp1-almost-no-hints-required\/","og_site_name":"Bob Beauchemin","article_published_time":"2009-03-07T19:29:00+00:00","article_modified_time":"2013-01-04T07:59:46+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\/spatial-indexes-in-sp1-almost-no-hints-required\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-in-sp1-almost-no-hints-required\/","name":"Spatial Indexes in SP1, almost no hints required - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2009-03-07T19:29:00+00:00","dateModified":"2013-01-04T07:59:46+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-in-sp1-almost-no-hints-required\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-in-sp1-almost-no-hints-required\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-in-sp1-almost-no-hints-required\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2008","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2008\/"},{"@type":"ListItem","position":3,"name":"Spatial Indexes in SP1, almost no hints required"}]},{"@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\/651","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=651"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/651\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=651"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=651"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=651"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}