{"id":662,"date":"2008-12-17T12:13:00","date_gmt":"2008-12-17T12:13:00","guid":{"rendered":"\/blogs\/bobb\/post\/Spatial-Index-Diagnostic-Procs-Filter-Output.aspx"},"modified":"2013-01-03T23:59:46","modified_gmt":"2013-01-04T07:59:46","slug":"spatial-index-diagnostic-procs-filter-output","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-index-diagnostic-procs-filter-output\/","title":{"rendered":"Spatial Index Diagnostic Procs – Filter Output"},"content":{"rendered":"

\nLast post discussed the filters. The procs report some raw numbers and some derived numbers. Here's a cheat sheet, although the info is all in the BOL.\n<\/p>\n

\nN = Number of rows in the table
\nO = Number of rows output
\nP = Number of rows selected by primary filter (by the index)
\nS = Number of rows selected by internal filter (by the index optimizations)\n<\/p>\n

\nThen,\n<\/p>\n

\nP-S       = Number_Of_Times_Secondary_Filter_Is_Called (number of times they ran the expensive operation)
\n(N-P)\/N = Percentage_Of_Rows_NotSelected_By_Primary_Filter
\nS\/P       = Percentage_Of_Primary_Filter_Rows_Selected_By_internal_Filter
\nS\/O       = Internal_Filter_Efficiency %
\nO\/P       = Primary_Filter_Efficiency %\n<\/p>\n

\n<Percentage_Of_Rows_NotSelected_By_Primary_Filter>9.942714746408677e+001
\n<Percentage_Of_Primary_Filter_Rows_Selected_By_Internal_Filter>9.095871716137836e+001
\n<Internal_Filter_Efficiency>9.593378913278158e+001
\n<Primary_Filter_Efficiency>9.481405663596043e+001\n<\/p>\n

\nSo how efficient is the index? Let's change this info into statements, using the values from the previous blog entry<\/a>:\n<\/p>\n

\nN = 511650, O = 2779, P = 2931, S = 2666\n<\/p>\n

\n<Number_Of_Times_Secondary_Filter_Is_Called>265<\/Number_Of_Times_Secondary_Filter_Is_Called>
\n<Percentage_Of_Rows_NotSelected_By_Primary_Filter>9.942714746408677e+001
\n<Percentage_Of_Primary_Filter_Rows_Selected_By_Internal_Filter>9.095871716137836e+001
\n<Internal_Filter_Efficiency>9.593378913278158e+001
\n<Primary_Filter_Efficiency>9.481405663596043e+001\n<\/p>\n

\n1. They had to run STIntersects 265 times, rather than 511650 times if they didn't use the index.
\n2. 99.4% of the rows were eliminated by using the index
\n3. The Internal_Filter (internal optimizations) eliminated 90% of the times they would have had to run Intersects, even if the used the index (primary_filter).
\n4. The Internal_Filter was able to "deduce" 95.9% of the rows in the answer.
\n5. The Primary_Filter was able to find 94.8% of the rows in the answer.\n<\/p>\n

\nPretty good index, huh? And pretty good internal filter. Probably is going to be used.\n<\/p>\n

\nLet's look at one more, but this time from using a geoemtry index. The index is over in geometry column that contains only points. The query sample is a polygon.\n<\/p>\n

\n<Base_Table_Rows>11267
\n<Number_Of_Rows_Selected_By_Primary_Filter>265
\n<Number_Of_Rows_Selected_By_Internal_Filter>149
\n<Number_Of_Times_Secondary_Filter_Is_Called>116
\n<Number_Of_Rows_Output>413
\n<Percentage_Of_Rows_NotSelected_By_Primary_Filter>9.764799857992367e+001
\n<Percentage_Of_Primary_Filter_Rows_Selected_By_Internal_Filter>5.622641509433962e+001
\n<Internal_Filter_Efficiency>3.607748184019371e+001
\n<Primary_Filter_Efficiency>1.558490566037736e+002\n<\/p>\n

\nNote that in this case, the internal filter only elimates 56.2% of the rows that the primary filter finds. It only finds 36% of the output rows. But note that in this case, primary filter efficiency = 155%. How can that be? Well, this is geometry and the primary filter didn't find all the rows in the answer. There must be some outside the bounding box. Remember that Primary_Filter_Efficiency is defined as Output_Rows \/ Primary_Filter rows.\n<\/p>\n

\nMore coming…<\/p>\n","protected":false},"excerpt":{"rendered":"

Last post discussed the filters. The procs report some raw numbers and some derived numbers. Here's a cheat sheet, although the info is all in the BOL. N = Number of rows in the table O = Number of rows output P = Number of rows selected by primary filter (by the index) S = […]<\/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-662","post","type-post","status-publish","format-standard","hentry","category-sql-server-2008","category-sql-server-spatial"],"yoast_head":"\nSpatial Index Diagnostic Procs - Filter Output - 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-index-diagnostic-procs-filter-output\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Spatial Index Diagnostic Procs - Filter Output - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"Last post discussed the filters. The procs report some raw numbers and some derived numbers. Here's a cheat sheet, although the info is all in the BOL. N = Number of rows in the table O = Number of rows output P = Number of rows selected by primary filter (by the index) S = […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-index-diagnostic-procs-filter-output\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2008-12-17T12:13: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=\"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\/spatial-index-diagnostic-procs-filter-output\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-index-diagnostic-procs-filter-output\/\",\"name\":\"Spatial Index Diagnostic Procs - Filter Output - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2008-12-17T12:13: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-index-diagnostic-procs-filter-output\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-index-diagnostic-procs-filter-output\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-index-diagnostic-procs-filter-output\/#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 Index Diagnostic Procs – Filter Output\"}]},{\"@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 Index Diagnostic Procs - Filter Output - 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-index-diagnostic-procs-filter-output\/","og_locale":"en_US","og_type":"article","og_title":"Spatial Index Diagnostic Procs - Filter Output - Bob Beauchemin","og_description":"Last post discussed the filters. The procs report some raw numbers and some derived numbers. Here's a cheat sheet, although the info is all in the BOL. N = Number of rows in the table O = Number of rows output P = Number of rows selected by primary filter (by the index) S = […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-index-diagnostic-procs-filter-output\/","og_site_name":"Bob Beauchemin","article_published_time":"2008-12-17T12:13: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":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-index-diagnostic-procs-filter-output\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-index-diagnostic-procs-filter-output\/","name":"Spatial Index Diagnostic Procs - Filter Output - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2008-12-17T12:13: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-index-diagnostic-procs-filter-output\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-index-diagnostic-procs-filter-output\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-index-diagnostic-procs-filter-output\/#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 Index Diagnostic Procs – Filter Output"}]},{"@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\/662","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=662"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/662\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=662"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=662"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=662"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}