\nI've devoted the last few blog entries to describing what's in that spatial index analysis proc output and how to use it and interpret the results. Just wanted to describe the rest of the information and how the information relates to what you'd find in a query plan that uses a spatial index.\n<\/p>\n
\nThe procs return:
\n1. Information about the index
\n2. Information about the query sample
\n3. How efficient the index is when used against the query sample\n<\/p>\n
\nThe information you get about the index is:
\n– Bounding Box dimensions – BOL says they are NULL for geography but they are always -180, -90, 180, 90 for geography
\n– GridLevelSize – 4 levels
\n– Cells_Per_Object – maximum tessellated cells per row, specified in DDL
\n– Page and Row counts for the spatial index
\n– Avg number of cells per base row (how many of the CellsPerObject were actually used)
\n– Height, Width, Area of a cell – Geography is always the same, but its nice to know how big the cells are compared to the other levels. Not sure what the unit of measure is for geography (BOL says "depends on SRID"), by looking at the number it doesn't look like it could be meters.
\n– CellAreaToBoundingBox percentage – 4 levels
\n– Total SRIDs found\n<\/p>\n
\nThe most interesting is likely the info about the number of cells in each level (including level0 – off the bounding box). Any levels that are 0 rows don't appear in the XML output. You get info for:
\n-Number of Object Cells for Index
\n-Number of Interior Cells
\n-Number of Intersecting Cells
\n-Number of Border Cells
\n-also some numbers about cells normalized to the leaf grid\n<\/p>\n
\nThe cells are useful in how they relate to the primary filter. The interior, intersecting, and border cells are used by the internal filter.\n<\/p>\n
\nFor the query sample, you get the same cells numbers. No "cells normalized to the leaf" however. BTW, for the geography index ignore the BOL comment that indicates that if Level0 of the query sample is 1 then index is not useable for this query. For geography its always 1.\n<\/p>\n
\nThis relates to the following information that appears in a query plan that uses spatial indexes.
\n1. All of the query samples (in the spatial predicate) are tessellated using the same parameters (density\/max cells)
\n2. The table is joined against all parent\/children of the index (the index levels are actually a hierarchy)
\n3. Index ranges are calculated
\n4. A join of the tesselated samples on index is done. This results in the number of rows selected by the primary filter.
\n5. The internal and secondary filters are applied in a separate query iterator (of type Filter) later on in the plan.\n<\/p>\n
\nThe upshot of this is that, not only is the index dimensions used to tessellate the base table rows, the same dimensions as the index are used to tessellate the query sample for the spatial predicate (query sample, in the sproc). So, for example, I'd always thought that spatial indexes over points should specify all-HIGH density and maybe 2 max cells per object. But if your query sample contains large polygons, those same dimensions will be used against the query sample too. So maybe all-HIGH density for point-based indexes isn't a hard and fast rule.\n<\/p>\n
\nHopefully this should give you enough information to investigate your indexes and queries using the spatial analysis stored procs. As far as patterns for "which specifications of index is best", more patterns will show up as I investigate more different patterns of spatial data. For now, I'm dividing these into three main categories:\n<\/p>\n
\n1. Spatial index over table of points, Query sample is polygon.
\n2. Spatial index over table of polygons, Query sample is point.
\n3. Spatial index over table of polygons, Query sample is polygon.\n<\/p>\n
\nWith the simplifying premise that linestrings are thin like a point (ie they won't fill a cell), but if you put a STBuffer around them, they're more like polygons.\n<\/p>\n
\nHappy index hunting.<\/p>\n","protected":false},"excerpt":{"rendered":"
I've devoted the last few blog entries to describing what's in that spatial index analysis proc output and how to use it and interpret the results. Just wanted to describe the rest of the information and how the information relates to what you'd find in a query plan that uses a spatial index. The procs […]<\/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-660","post","type-post","status-publish","format-standard","hentry","category-sql-server-2008","category-sql-server-spatial"],"yoast_head":"\n