{"id":669,"date":"2008-11-05T06:49:00","date_gmt":"2008-11-05T06:49:00","guid":{"rendered":"\/blogs\/bobb\/post\/Using-the-spatial-index-diagnostic-stored-procedures.aspx"},"modified":"2013-01-03T23:59:47","modified_gmt":"2013-01-04T07:59:47","slug":"using-the-spatial-index-diagnostic-stored-procedures","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-spatial-index-diagnostic-stored-procedures\/","title":{"rendered":"Using the spatial index diagnostic stored procedures"},"content":{"rendered":"<p>\nI remember hearing during the SQL Server 2008 beta that there would be diagnostic stored procedures that would produce information about spatial indexes and help in troubleshooting why an index was not being used. In addition, these procedures would help in determining the best spatial index. A few weeks ago I had the good fortune to run across these procedures.\n<\/p>\n<p>\nsp_help_spatial_geometry_index and sp_help_spatial_geography_index put out information as columns<br \/>\nsp_help_spatial_geometry_index_xml and sp_help_spatial_geography_index_xml put out XML format\n<\/p>\n<p>\nEach procedure takes a table name, index name, and sample query. There&#39;s an option for verbose or basic properties. The parameters and output are doc&#39;d in BOL. Rather than repeat the doc, I&#39;d like to walk through a problem and show how you&#39;d interpret the output.\n<\/p>\n<p>\nSay that you are trying to decide between an index with high and medium density to run a query similar to the following:\n<\/p>\n<p>\nDeclare @geometry geometry = <br \/>\n&nbsp;Geometry::STGeomFromText(&#39;POLYGON((500000 300000,500000 340000,540000 340000,540000 300000,500000 300000))&#39;, 0)<br \/>\nexec sp_executesql N&#39;select * from spatial_table where LOCATION.STIntersects(@g) = 1&#39;, N&#39;@g geometry&#39;, @geometry\n<\/p>\n<p>\nCreate both indexes and run the procedure against each one, using the sample query. There is a lot of very detailed information about the index itself because I was running in verbose mode, including information about the number of cells at each level of tesselation. Before looking at this information, it would be good to review <a href=\"http:\/\/blogs.msdn.com\/b\/isaac\/archive\/2008\/03\/01\/basic-multi-level-grids.aspx\" class=\"broken_link\">Isaac&#39;s excellent blog post <\/a>on how tesselation works. The most useful info though, was contained in the &quot;Number of rows selected by &#8230; filter&quot; properties and filter efficiency properties. In my case there they were\n<\/p>\n<p>\nHigh density index:<br \/>\nNumber_Of_Rows_Selected_By_Primary_Filter:&nbsp; 2931<br \/>\nNumber_Of_Rows_Selected_By_Internal_Filter: 2399 <br \/>\nNumber_Of_Times_Secondary_Filter_Is_Called:&nbsp; 532\n<\/p>\n<p>\nMedium density index:\n<\/p>\n<p>\nNumber_Of_Rows_Selected_By_Primary_Filter:&nbsp; 2931<br \/>\nNumber_Of_Rows_Selected_By_Internal_Filter: 2666 <br \/>\nNumber_Of_Times_Secondary_Filter_Is_Called:&nbsp; 265\n<\/p>\n<p>\nNote that &quot;primary filter&quot; in this case is the spatial index in question, the goal is to have the rows selected by the primary or internal filter rather than run the secondary filter (the actual Intersects operation). Note that there can be false positives, the actual number of rows returned was not 2931 but 2779.\n<\/p>\n<p>\nThe best way to judge the effectiveness of the index, however, is with the property Internal_Filter_Efficiency or Primary_Filter_Efficiency.&nbsp; Internal_Filter_Efficiency, for example, showed the high density index at 86% and the medium denisty index at 96%. Clearly, the medium density index is a better choice for this query. In addition, looking at the statistics on the index itself shows that the medium density index is a smaller index with less rows and index row per base row. The figures were:\n<\/p>\n<p>\nHigh density:<br \/>\nTotal_Primary_Index_Rows: 5273534 rows<br \/>\nAverage_Number_Of_Index_Rows_Per_Base_Row : 10\n<\/p>\n<p>\nMedium density:<br \/>\nTotal_Primary_Index_Rows: 1965865 rows<br \/>\nAverage_Number_Of_Index_Rows_Per_Base_Row : 3\n<\/p>\n<p>\nThere is also a property, Total_Number_Of_ObjectCells_In_Level0_For_QuerySample, that will tell you if the index *can* be used. A non-zero value indicates this and properties that will assist in determining whether to adjust the bounding box on a geometry index.\n<\/p>\n<p>\nHope this will give you a good head start in working with these excellent informational and diagnostic stored procedures.\n<\/p>\n<p>\n&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I remember hearing during the SQL Server 2008 beta that there would be diagnostic stored procedures that would produce information about spatial indexes and help in troubleshooting why an index was not being used. In addition, these procedures would help in determining the best spatial index. A few weeks ago I had the good fortune [&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-669","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>Using the spatial index diagnostic stored procedures - 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\/using-the-spatial-index-diagnostic-stored-procedures\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using the spatial index diagnostic stored procedures - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"I remember hearing during the SQL Server 2008 beta that there would be diagnostic stored procedures that would produce information about spatial indexes and help in troubleshooting why an index was not being used. In addition, these procedures would help in determining the best spatial index. A few weeks ago I had the good fortune [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-spatial-index-diagnostic-stored-procedures\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2008-11-05T06:49:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T07:59:47+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\/using-the-spatial-index-diagnostic-stored-procedures\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-spatial-index-diagnostic-stored-procedures\/\",\"name\":\"Using the spatial index diagnostic stored procedures - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2008-11-05T06:49:00+00:00\",\"dateModified\":\"2013-01-04T07:59:47+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-spatial-index-diagnostic-stored-procedures\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-spatial-index-diagnostic-stored-procedures\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-spatial-index-diagnostic-stored-procedures\/#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\":\"Using the spatial index diagnostic stored procedures\"}]},{\"@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":"Using the spatial index diagnostic stored procedures - 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\/using-the-spatial-index-diagnostic-stored-procedures\/","og_locale":"en_US","og_type":"article","og_title":"Using the spatial index diagnostic stored procedures - Bob Beauchemin","og_description":"I remember hearing during the SQL Server 2008 beta that there would be diagnostic stored procedures that would produce information about spatial indexes and help in troubleshooting why an index was not being used. In addition, these procedures would help in determining the best spatial index. A few weeks ago I had the good fortune [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-spatial-index-diagnostic-stored-procedures\/","og_site_name":"Bob Beauchemin","article_published_time":"2008-11-05T06:49:00+00:00","article_modified_time":"2013-01-04T07:59:47+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\/using-the-spatial-index-diagnostic-stored-procedures\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-spatial-index-diagnostic-stored-procedures\/","name":"Using the spatial index diagnostic stored procedures - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2008-11-05T06:49:00+00:00","dateModified":"2013-01-04T07:59:47+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-spatial-index-diagnostic-stored-procedures\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-spatial-index-diagnostic-stored-procedures\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-the-spatial-index-diagnostic-stored-procedures\/#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":"Using the spatial index diagnostic stored procedures"}]},{"@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\/669","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=669"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/669\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=669"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=669"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=669"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}