{"id":481,"date":"2012-06-21T11:43:00","date_gmt":"2012-06-21T11:43:00","guid":{"rendered":"\/blogs\/bobb\/post\/Does-everybody-get-that-(Spatial-Index-Reprise).aspx"},"modified":"2013-01-03T23:59:22","modified_gmt":"2013-01-04T07:59:22","slug":"does-everybody-get-that-spatial-index-reprise","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-spatial-index-reprise\/","title":{"rendered":"Does everybody get that? (Spatial Index Reprise)"},"content":{"rendered":"<p>\nThis post is an example of the &quot;bring your own problem&quot; approach in the classroom, inspired by upcoming <a href=\"http:\/\/www.sqlskills.com\/immersionevents.asp\" class=\"broken_link\">SQLskills Immersion Events<\/a> in August. See previous <a href=\"http:\/\/3.209.169.194\/blogs\/bobb\/does-everybody-get-that-sqlclr\/\" class=\"broken_link\">posting<\/a> for the reason behind the blog post title.\n<\/p>\n<p>\nI believe it was John Lennon who wrote: &quot;You say you got a real solution. Well, you know, We&#39;d all love to see the plan&quot;. I&#39;m&nbsp;certain he wasn&#39;t referring to the query plans I&#39;d &quot;love to see&quot; when I am diagnosing a problem, but that&#39;s the cognitive overlap that happens when you&#39;re listening to tunes while troubleshooting. And sometimes just &quot;seeing the (estimated) plan&quot; isn&#39;t enough.\n<\/p>\n<p>\nI&#39;ve been working with spatial data and spatial indexing for quite a while now. And have already published a blog entry on <a href=\"http:\/\/3.209.169.194\/blogs\/bobb\/how-to-ensure-your-spatial-index-is-being-used\/\" class=\"broken_link\">how to ensure your spatial index is used<\/a>. But, since then, I&#39;ve answered countless questions in classes, at conferences, and from clients about why the spatial index isn&#39;t being used without a hint.\n<\/p>\n<p>\nThe top two things to be sure of for starters are:<br \/>\n&nbsp;1. Make sure you are on at least SQL Server 2008 SP1 to <br \/>\n&nbsp;2. Make sure the query processor can sniff your spatial parameter values (i.e. use sproc or sp_executesql with spatial parameter passed in)\n<\/p>\n<p>\nHere&#39;s two of more curious ones I&#39;ve gotten lately. First one is client is using 4 processor machine. All is going well, but they figure it will be faster with a 24-proc machine. The spatial queries go slower in testing, by orders of magnitude. Turns out the 4-proc was using spatial index. With one tester on the 24-proc, the optimizer decided it was best to use a highly parallelised non-spatial-index plan. Setting maxdop down (or using a hint) was a temporary fix. The optimizer algorithm was changed along the way (2008 R2 SP1, IIRC). But, to think of this problem another way, unless you have an entire 24-proc machine to yourself, the actual number of processors available won&#39;t usually be equal to the total number of processors on the machine. In fact, on a loaded system, you may only get one or two processors at runtime. So setting maxdop down so the spatial index will be used or hinting isn&#39;t a bad idea. BTW, the spatial index plans can and do use parallelism for most or all of the plan if you have enough available processors.\n<\/p>\n<p>\nThe optimizer is superb at &quot;reasoning&quot; over most relational queries and indexes, but tends to underestimate the spatial index, which can have a dramatic effect when used. I think this happens for two reasons, first is an underestimation of the expense of STIntersects(), especially when one of the spatial operands is a complex polygon. Second is overhead of doing a seek into the base table for each candidate row, which is a side-effect of using the spatial index. So also choosing the correct spatial index parameters is crucial for cutting down on the number of seeks. Second one was that a query in this form: &#8230;WHERE spatialcol.STDistance(@spatialparm)*(conversionfactor) &lt; number. This not only wouldn&#39;t use the spatial index, you couldn&#39;t even hint it. The reason for this is the spatial index is only usable for certain forms, using &#8230;WHERE spatialcol.STDistance(@spatialparm)= number\/conversionfactor made using the spatial index viable.\n<\/p>\n<p>\nSo, to wrap up, if your spatial query does not use the spatial index, or seems to run too long, try hinting the spatial index first. If you get an error, you&#39;re not using a &quot;spatial index viable&quot; predicate. Once you&#39;ve got the query &quot;hintable&quot;, then you can work having the optimizer choose the spatial index. And compare clock-on-the-wall time with and without the spatial index. Not the estimated query plan.\n<\/p>\n<p>\nDoes everybobdy get that?\n<\/p>\n<p>\n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This post is an example of the &quot;bring your own problem&quot; approach in the classroom, inspired by upcoming SQLskills Immersion Events in August. See previous posting for the reason behind the blog post title. I believe it was John Lennon who wrote: &quot;You say you got a real solution. Well, you know, We&#39;d all love [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19,36],"tags":[],"class_list":["post-481","post","type-post","status-publish","format-standard","hentry","category-performance","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>Does everybody get that? (Spatial Index Reprise) - 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\/does-everybody-get-that-spatial-index-reprise\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Does everybody get that? (Spatial Index Reprise) - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"This post is an example of the &quot;bring your own problem&quot; approach in the classroom, inspired by upcoming SQLskills Immersion Events in August. See previous posting for the reason behind the blog post title. I believe it was John Lennon who wrote: &quot;You say you got a real solution. Well, you know, We&#039;d all love [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-spatial-index-reprise\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2012-06-21T11:43:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T07:59:22+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=\"3 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\/does-everybody-get-that-spatial-index-reprise\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-spatial-index-reprise\/\",\"name\":\"Does everybody get that? (Spatial Index Reprise) - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2012-06-21T11:43:00+00:00\",\"dateModified\":\"2013-01-04T07:59:22+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-spatial-index-reprise\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-spatial-index-reprise\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-spatial-index-reprise\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Performance\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/performance\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Does everybody get that? (Spatial Index Reprise)\"}]},{\"@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":"Does everybody get that? (Spatial Index Reprise) - 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\/does-everybody-get-that-spatial-index-reprise\/","og_locale":"en_US","og_type":"article","og_title":"Does everybody get that? (Spatial Index Reprise) - Bob Beauchemin","og_description":"This post is an example of the &quot;bring your own problem&quot; approach in the classroom, inspired by upcoming SQLskills Immersion Events in August. See previous posting for the reason behind the blog post title. I believe it was John Lennon who wrote: &quot;You say you got a real solution. Well, you know, We&#39;d all love [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-spatial-index-reprise\/","og_site_name":"Bob Beauchemin","article_published_time":"2012-06-21T11:43:00+00:00","article_modified_time":"2013-01-04T07:59:22+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-spatial-index-reprise\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-spatial-index-reprise\/","name":"Does everybody get that? (Spatial Index Reprise) - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2012-06-21T11:43:00+00:00","dateModified":"2013-01-04T07:59:22+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-spatial-index-reprise\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-spatial-index-reprise\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-spatial-index-reprise\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Performance","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/performance\/"},{"@type":"ListItem","position":3,"name":"Does everybody get that? (Spatial Index Reprise)"}]},{"@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\/481","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=481"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/481\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=481"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=481"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=481"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}