Transcript Michael Rys ([email protected]) Principal Program Manager Lead Microsoft Corp. Session Code: DAT403 Q: Why is my Query so Slow? A: Usually because the index.

Michael Rys ([email protected])
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
Hinting the Index
Spatial indexes can be forced if needed.
SELECT *
FROM T WITH(INDEX(T_g_idx))
WHERE g.STIntersects(@x) = 1
Use SQL Server 2008 SP1!
But Why Isn't My Index Used?
Plan choice is cost-based
QO uses various information, including cardinality
EXEC
sp_executesql
SELECT
*@x geometry = 'POINT (0 0)'
DECLARE
*
FROM
TN'SELECT
SELECT
*
T
WHERE
T.g.STIntersects('POINT
(0 0)') = 1
FROM TFROM
T.g.STIntersects(@x)
WHERE WHERE
T.g.STIntersects(@x)
= 1 = 1',
N'@x geometry', N'POINT (0 0)'
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
Spatial Indexing Basics
C
D A
B
E
D A
B
Primary Filter
(Index lookup)
A
Secondary Filter
(Original predicate)
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
B
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
Requires bounding box
Only one grid
No bounding box
Two top-level projection grids
Secondary
Primary
Filter
IndexingFilter
Phase
1
2
15
16
4
3
14
13
5
8
9
12
6
7
10
11
5. Apply
1.
2.
3.
4.
Overlay
Identify
Intersecting
actual
agrids
grid
grids
CLR
for
on query
spatial
method
the
identifies
spatial
object(s)
object
oncandidates
object
to
candidates
store in index
to find matches
1.
3.
2.
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)
Spatial encoding
Reference ID
Varbinary(5)
1 – guarantee
that object partially covers cell
gridHave
cellcell
idto be the same to
15 columns and 2
895
byteof
limitation
– object
covers
produce match
Prim_key
geometry
1
g1
2
g2
3
g3
Base Table T
CREATE SPATIAL INDEX sixd
ON T(geography)
Prim_key
cell_id
srid
cell_attr
1
0x00007
42
0
3
0x00007
42
1
3
0x0000A
42
2
3
0x0000B
42
0
3
0x0000C
42
1
1
0x0000D
42
0
2
0x00014
42
1
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.
13
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
How Costing is Done
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
Understanding the Index Query Plan
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
Spatial Index S
7.2
7.2.4
Understanding the Index Query Plan
Optional Sort
Remove dup
ranges
T(@g)
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
Parameter
Type
Description
@tabname
nvarchar(776)
the name of the table for which the index has been
specified
@indexname
sysname
the index name to be investigated
@verboseoutput
tinyint
0 core set of properties is reported
1 all properties are being reported
@query_sample
geometry
A representative query sample that will be used to
test the usefulness of the index. It may be a
representative object or a query window.
Results in property name/value pair table of the format:
PropName: nvarchar(256)
PropValue: sql_variant
sys.sp_help_spatial_geography_index_xml
Arguments
Parameter
Type
Description
@tabname
nvarchar(776)
the name of the table for which the index has
been specified
@indexname
sysname
the index name to be investigated
@verboseoutput
tinyint
0 core set of properties is reported
1 all properties are being reported
@query_sample
geography
A representative query sample that will be
used to test the usefulness of the index. It
may be a representative object or a query
window.
@xml_output
xml
This is an output parameter that contains the
returned properties in an XML fragment
Some of the returned Properties
Property
Type
Description
Number_Of_Rows_Selected_By_
Primary_Filter
Number_Of_Rows_Selected_By_
Internal_Filter
bigint
Core
bigint
Core
Number_Of_Times_Secondary_Fi bigint
lter_Is_Called
Percentage_Of_Rows_NotSelecte float
d_By_Primary_Filter
Core
Percentage_Of_Primary_Filter_R float
ows_Selected_By_Internal_Filter
Core
Number_Of_Rows_Output
bigint
Core
P = Number of rows selected by the
primary filter.
S = Number of rows selected by the
internal filter. For these rows, the secondary
filter is not called.
Number of times the secondary filter is
called.
Suppose there are N rows in the base table,
suppose P are selected by the primary filter.
This is (N-P)/N as percentage.
This is S/P as a percentage. The higher the
percentage, the better is the index in
avoiding the more expensive secondary
filter.
O=Number of rows output by the query.
Internal_Filter_Efficiency
float
Core
This is S/O as a percentage.
Primary_Filter_Efficiency
float
Core
This is O/P as a percentage. The higher the
efficiency is, the less false positives have to
be processed by the secondary filter.
Core
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 nonspatial 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!
http://connect.microsoft.com/sqlserver/feedback
Resources
www.microsoft.com/teched
www.microsoft.com/learning
Sessions On-Demand & Community
Microsoft Certification & Training Resources
http://microsoft.com/technet
http://microsoft.com/msdn
Resources for IT Professionals
Resources for Developers
Complete an evaluation
on CommNet and enter to
win an Xbox 360 Elite!
© 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.