360 likes | 603 Views
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?
E N D
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? A: SELECT * FROM T WHERE g.STIntersects(@x) = 1 NO INDEX INDEX!
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))
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
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
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!
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
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
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
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
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
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.
demo Indexing and Performance Some of the data provided by Navteq
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
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
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
Understanding the Index Query Plan T(@g) Optional Sort Remove dup ranges Ranges Spatial Index Seek
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
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
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
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
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.
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
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
sys.sp_help_spatial_geometry_index • Arguments • Results in property name/value pair table of the format:
sys.sp_help_spatial_geography_index_xml • Arguments
demo Indexing Supportability
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
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!
question & answer Meet me in the Ask-the-Experts pavilion! http://connect.microsoft.com/sqlserver/feedback
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
Complete an evaluation on CommNet and enter to win an Xbox 360 Elite!
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.