Make Spatial Search faster in Sql server

Question

I am trying to use Sql server 2012 Spatial Data to store geographical location for my customers. I have a search screen where i need to find all the customers, 50 miles within an input location.

I have 3 million rows and when i try the query below without Index hint, its taking 3 to 4 seconds. But with query hint its instantaneous.

Select top 10 * from Customer with (Index(IDX_Location) )
where Location.STDistance(0xE6100000010C0600004075383E40FEFFFF1F706354C0) 
<= (50 *    1609.344)

Am i using this incorrectly ? Is it okay to use the hint only for Spatial searches ? I cannot afford a 4 second delay for every search,

UPDATE

This is Execution plan without Query Hint

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.2218.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="10" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0371276" StatementText="      Select top 10 * from Customer &#xD;&#xA;   where Location.STDistance(0xE6100000010C0600004075383E40FEFFFF1F706354C0) &lt;= (50 * 1609.344)&#xD;&#xA;" StatementType="SELECT" QueryHash="0x8FD6DFA68AC6E7AF" QueryPlanHash="0xFD53392CE2607CF0" RetrievedFromCache="true">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan CachedPlanSize="40" CompileTime="9" CompileCPU="8" CompileMemory="344">
            <GuessedSelectivity>
              <Spatial />
            </GuessedSelectivity>
            <Warnings SpatialGuess="true" />
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="414457" EstimatedPagesCached="51807" EstimatedAvailableDegreeOfParallelism="2" />
            <RelOp AvgRowSize="13195" EstimateCPU="1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="10" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0371276">
              <OutputList>
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Id" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="FirstName" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="LastName" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="CountryCd" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Zip" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="CustomerSummary" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="IFollowCount" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="FollowingMeCount" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="PhotoUrl" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="IsTutor" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="SearchName" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="City" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="IsStudent" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Competition" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="MediaBroadcast" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="DiplomasHonors" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Location" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Longitude" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Latitude" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="contact_email" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="contact_Ph" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Link1" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Link2" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Link3" />
                <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="HeadLine" />
              </OutputList>
              <Top RowCount="false" IsPercent="false" WithTies="false">
                <TopExpression>
                  <ScalarOperator ScalarString="(10)">
                    <Const ConstValue="(10)" />
                  </ScalarOperator>
                </TopExpression>
                <RelOp AvgRowSize="13195" EstimateCPU="3467.57" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="10" LogicalOp="Filter" NodeId="1" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.0371266">
                  <OutputList>
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Id" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="FirstName" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="LastName" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="CountryCd" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Zip" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="CustomerSummary" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="IFollowCount" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="FollowingMeCount" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="PhotoUrl" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="IsTutor" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="SearchName" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="City" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="IsStudent" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Competition" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="MediaBroadcast" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="DiplomasHonors" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Location" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Longitude" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Latitude" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="contact_email" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="contact_Ph" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Link1" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Link2" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Link3" />
                    <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="HeadLine" />
                  </OutputList>
                  <Filter StartupExpression="false">
                    <RelOp AvgRowSize="13195" EstimateCPU="3.81189" EstimateIO="48.435" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="33.3333" LogicalOp="Clustered Index Scan" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00377058" TableCardinality="3465210">
                      <OutputList>
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Id" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="FirstName" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="LastName" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="CountryCd" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Zip" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="CustomerSummary" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="IFollowCount" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="FollowingMeCount" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="PhotoUrl" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="IsTutor" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="SearchName" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="City" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="IsStudent" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Competition" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="MediaBroadcast" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="DiplomasHonors" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Location" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Longitude" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Latitude" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="contact_email" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="contact_Ph" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Link1" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Link2" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Link3" />
                        <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="HeadLine" />
                      </OutputList>
                      <Warnings>
                        <ColumnsWithNoStatistics>
                          <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Location" />
                        </ColumnsWithNoStatistics>
                      </Warnings>
                      <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Id" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="FirstName" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="LastName" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="CountryCd" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Zip" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="CustomerSummary" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="IFollowCount" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="FollowingMeCount" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="PhotoUrl" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="IsTutor" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="SearchName" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="City" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="IsStudent" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Competition" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="MediaBroadcast" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="DiplomasHonors" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Location" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Longitude" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Latitude" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="contact_email" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="contact_Ph" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Link1" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Link2" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Link3" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="HeadLine" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Index="[PK_Customer]" IndexKind="Clustered" />
                      </IndexScan>
                    </RelOp>
                    <Predicate>
                      <ScalarOperator ScalarString="[MyDataBase].[dbo].[Customer].[Location].STDistance(CONVERT_IMPLICIT(varbinary(max),0xE6100000010C0600004075383E40FEFFFF1F706354C0,0).)&lt;=(8.046720000000000e+004)">
                        <Compare CompareOp="LE">
                          <ScalarOperator>
                            <UDTMethod>
                              <CLRFunction Assembly="Microsoft.SqlServer.Types" Class="Microsoft.SqlServer.Types.SqlGeography" Method="STDistance" />
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[MyDataBase]" Schema="[dbo]" Table="[Customer]" Column="Location" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Column="ConstExpr1003">
                                    <ScalarOperator>
                                      <UDTMethod>
                                        <CLRFunction Assembly="Microsoft.SqlServer.Types" Class="Microsoft.SqlServer.Types.SqlGeography" />
                                        <ScalarOperator>
                                          <Convert DataType="varbinary(max)" Length="2147483647" Style="0" Implicit="true">
                                            <ScalarOperator>
                                              <Const ConstValue="0xE6100000010C0600004075383E40FEFFFF1F706354C0" />
                                            </ScalarOperator>
                                          </Convert>
                                        </ScalarOperator>
                                      </UDTMethod>
                                    </ScalarOperator>
                                  </ColumnReference>
                                </Identifier>
                              </ScalarOperator>
                            </UDTMethod>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Const ConstValue="(8.046720000000000e+004)" />
                          </ScalarOperator>
                        </Compare>
                      </ScalarOperator>
                    </Predicate>
                  </Filter>
                </RelOp>
              </Top>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Thanks !

Answer 1

Unfortunately the SQL Server Query Optimizer tends to ignore the spatial index frequently although it might speed up the query dramatically. Its a known issue, that it is often better to use an index hint for spatial queries instead of trusting the QO to use it by itself.

You can try to tune your spatial index, so perhaps the query optimizer then might recognize it (never prooved that by myself, only heard that this is a possible solution).

The stored procedure sp_help_spatial_geography_index gives you some parameters to make it simpler for you to select the right spatial index grid density depending on your data points distribution and clustering.

Due to the fact that a 50 miles radius is quite a big area, I guess it might help to use the "LOW" grid density at all or at least the upper most index levels, so that space is separated into less grid cells, and the query optimizer gets less index cells returned as candidates, which should have an impact on its decision to use the spatial index.