1 / 36

Developing with SQL Server Spatial: Deep Dive into Spatial Indexing

Developing with SQL Server Spatial: Deep Dive into Spatial Indexing. Michael Rys (mrys@microsoft .com) Principal Program Manager Lead Microsoft Corp. Session Code: DAT403. Q: Why is my Query so Slow?. A: Usually because the index isn’t being used. Q : How do I tell?

miya
Download Presentation

Developing with SQL Server Spatial: Deep Dive into Spatial Indexing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Developing with SQL Server Spatial: Deep Dive into Spatial Indexing Michael Rys (mrys@microsoft.com) Principal Program Manager Lead Microsoft Corp. Session Code: DAT403

  2. Q: Why is my Query so Slow? A: Usually because the index isn’t being used. Q: How do I tell? A: SELECT * FROM T WHERE g.STIntersects(@x) = 1 NO INDEX INDEX!

  3. Hinting the Index • Spatial indexes can be forced if needed. SELECT * FROM T WHERE g.STIntersects(@x) = 1 • Use SQL Server 2008 SP1! WITH(INDEX(T_g_idx))

  4. But Why Isn't My Index Used? • Plan choice is cost-based • QO uses various information, including cardinality • When can we estimate cardinality? • Variables: never • Literals: not for spatial since they are not literals under the covers • Parameters: yes, but cached, so first call matters EXEC sp_executesql N'SELECT * FROM T WHERE T.g.STIntersects(@x) = 1', N'@x geometry', N'POINT (0 0)' SELECT * FROM T WHERE T.g.STIntersects('POINT (0 0)') = 1 DECLARE @x geometry = 'POINT (0 0)' SELECT * FROM T WHERE T.g.STIntersects(@x) = 1

  5. Spatial Indexing Basics D C A B D A B • In general, split predicates in two • Primary filter finds all candidates, possibly with false positives (but never false negatives) • Secondary filter removes false positives • The index provides our primary filter • Original predicate is our secondary filter • Some tweaks to this scheme • Sometimes possible to skip secondary filter A B Primary Filter (Index lookup) Secondary Filter (Original predicate) E

  6. Using B+-Trees for Spatial Index • SQL Server has B+-Trees • Spatial indexing is usually done through other structures • Quad tree, R-Tree • Challenge: How do we repurpose the B+-Tree to handle spatial queries? • Add a level of indirection!

  7. Mapping to the B+-Tree • B+-Trees handle linearly ordered sets well • We need to somehow linearly order 2D space • Either the plane or the globe • We want a locality-preserving mapping from the original space to the line • i.e., close objects should be close in the index • Can’t be done, but we can approximate it

  8. SQL Server 2008 Indexing Story Planar Index Geographic Index No bounding box Two top-level projection grids • Requires bounding box • Only one grid Secondary Filter Primary Filter Indexing Phase 1. 3. 2. 1. Overlay a grid on the spatial object 2. Identify grids for spatial object to store in index 3. Identify grids for query object(s) 4. Intersecting grids identifies candidates 5. Apply actual CLR method on candidates to find matches

  9. SQL Server 2008 Indexing Story • Multi-Level Grid • Much more flexible than a simple grid • Hilbert numbering • Modified adaptable QuadTree • Grid index features • 4 levels • Customizable grid subdivisions • Customizable maximum number of cells per object

  10. Multi-Level Grid /4/2/3/1 / (“cell 0”) Deepest-cell Optimization: Only keep the lowest level cell in index Covering Optimization: Only record higher level cells when all lower cells are completely covered by the object Cell-per-object Optimization: User restricts max number of cells per object

  11. Implementation of the Index • Persist a table-valued function • Internally rewrite queries to use the table 0 – cell at least touches the object (but not 1 or 2) 1 – guarantee that object partially covers cell 2 – object covers cell Spatial Reference ID Have to be the same to produce match Varbinary(5) encoding of grid cell id 15 columns and 895 byte limitation Base Table T • CREATE SPATIAL INDEX sixd ON T(geography) Internal Table for sixd

  12. Index Creation and Maintenance Create index example GEOMETRY: CREATE SPATIAL INDEX sixd ON spatial_table(geom_column) WITH ( BOUNDING_BOX = (0, 0, 500, 500), GRIDS = (LOW, LOW, MEDIUM, HIGH), CELLS_PER_OBJECT = 20) Create index example GEOGRAPHY: CREATE SPATIAL INDEX sixd ON spatial_table(geogr_column) WITH ( GRIDS = (LOW, LOW, MEDIUM, HIGH), CELLS_PER_OBJECT = 20) Use ALTER and DROP INDEX for maintenance.

  13. demo Indexing and Performance Some of the data provided by Navteq

  14. Spatial Methods supported by Index • Geometry: • STIntersects() = 1 • STOverlaps() = 1 • STEquals()= 1 • STTouches() = 1 • STWithin() = 1 • STContains() = 1 • STDistance() < val • STDistance() <= val • Filter() = 1 • Geography: • STIntersects() = 1 • STEquals()= 1 • STDistance() < val • STDistance() <= val • Filter() = 1

  15. The stats on the index contain a trie constructed on the string form of the packed binary(5) typed CellID. When a window query is compiled with a sniffable window object, the tessellation function on the window object is run at compile time. The results are used to construct a trie for use during compilation. May lead to wrong compilation for later objects No costing on: Local variables, constants, results of expressions Use different indices and different stored procs to account for different query characteristics How Costing is Done

  16. Understanding the Index Query Plan

  17. Seeking into a Spatial Index • Minimize I/O and random I/O • Intuition: small windows should touch small portions of the index • A cell 7.2.4 matches • Itself • Ancestors • Descendants 7.2 7.2.4 7 Spatial Index S

  18. Understanding the Index Query Plan T(@g) Optional Sort Remove dup ranges Ranges Spatial Index Seek

  19. Other Query Processing Support • Index intersection • Enables efficient mixing of spatial and non-spatial predicates • Matching • Distance queries: convert to STIntersects • Commutativity: a.STIntersects(b) = b.STIntersects(a) • Dual: a.STContains(b) = b.STWithin(a) • Multiple spatial indexes on the same column • Various bounding boxes, granularities • Outer references as window objects • Enables spatial join to use one index

  20. Limitations of Spatial Plan Selection • Off whenever window object is not a parameter: • Spatial join (window is an outer reference) • Local variable, string constant, or complex expression • Has the classic SQL Server parameter-sensitivity problem • SQL compiles once for one parameter value and reuses the plan for all parameter values • Different plans for different sizes of window require application logic to bucketize the windows

  21. Index Support • Can be built in parallel • Can be hinted • File groups/Partitioning • Aligned to base table or Separate file group • Full rebuild only • New catalog views, DDL Events • DBCC Checks • Supportability stored procedures • Not supported • Online rebuild • Database Tuning advisor

  22. SET Options • Spatial indexes requires: • ANSI_NULLS: ON • ANSI_PADDING: ON • ANSI_WARNINGS: ON • CONCAT_NULL_YIELDS_NULL: ON • NUMERIC_ROUNDABORT: OFF • QUOTED_IDENTIFIER: ON

  23. Index Hinting • FROM T WITH (INDEX (<Spatial_idxname>)). • Spatial index is treated the same way a non-clustered index is • the order of the hint is reflected in the order of the indexes in the plan • multiple index hints are concatenated • no duplicates are allowed • The following restrictions exist: • The spatial index must be either first in the first index hint or last in the last index hint for a given table. • Only one spatial index can be specified in any index hint for a given table.

  24. Spatial Catalog Views • New sys.spatial_indexes catalog view • New sys.spatial_index_tessellations catalog view • New entries in sys.indexes for a spatial index: • A clustered index on the internal table of the spatial index • A spatial index (type = 4) for spatial index • A new entry in sys.internal_tables • A new entry to sys.index_columns

  25. Indexing Support Procedures • sys.sp_help_spatial_geometry_index • sys.sp_help_spatial_geometry_index_xml • sys.sp_help_spatial_geography_index • sys.sp_help_spatial_geography_index_xml Provide information about index: • 64 properties • 10 of which are considered core

  26. sys.sp_help_spatial_geometry_index • Arguments • Results in property name/value pair table of the format:

  27. sys.sp_help_spatial_geography_index_xml • Arguments

  28. Some of the returned Properties

  29. demo Indexing Supportability

  30. What to do if my Spatial Query is slow? • Make sure you are running SQL Server 2008 SP1 • Check query plan for use of index • Make sure it is a supported operation • Hint the index (and/or a different join type) • Do not use a spatial index when there is a highly selective non-spatial predicate • Run above index support procedure: • Assess effectiveness of primary filter (Primary_Filter_Efficiency) • Assess effectiveness of internal filter (Internal_Filter_Efficiency) • Redefine or define a new index with better characteristics • More appropriate bounding box for GEOMETRY • Better grid densities

  31. Related Content • Breakout Sessions • DAT307: Developing with SQL Server Spatial: Flat Maps to Round Earth (Wednesday Nov 11th, 9:00 to 10:15) • Weblog • http://blogs.msdn.com/isaac • http://blogs.msdn.com/edkatibah • http://johanneskebeck.spaces.live.com/ • http://sqlblog.com/blogs/michael_rys/ • Forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1629&SiteID=1 • Whitepapers, Websites & Code • Spatial Site: http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx • SQL Spatial Codeplex: http://www.codeplex.com/sqlspatialtools • http://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx • http://www.codeplex.com/ProjNET • http://www.geoquery2008.com/ • SIGMOD 2008 Paper: Spatial Indexing in Microsoft SQL Server 2008 • And of course Books Online!

  32. question & answer Meet me in the Ask-the-Experts pavilion! http://connect.microsoft.com/sqlserver/feedback

  33. Required Slide Speakers, TechEd 2009 is not producing a DVD. Please announce that attendees can access session recordings at TechEd Online. Resources • www.microsoft.com/teched Sessions On-Demand & Community • www.microsoft.com/learning • Microsoft Certification & Training Resources • http://microsoft.com/technet • Resources for IT Professionals • http://microsoft.com/msdn Resources for Developers

  34. Complete an evaluation on CommNet and enter to win an Xbox 360 Elite!

  35. Required Slide © 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

More Related