I have a spatial index and the following query:

FROM [dbo].[DataSource01] DS1
INNER JOIN [dbo].[DataSource02] DS2 WITH (INDEX = [IX_DataSource02_Location])
    ON DS1.[ID] = DS2.[ID]
WHERE DS2.[Location].STDistance(@Location) < 144840.96; -- 90 miles in meters

where the @Location is the current user location. For two different users, I get two different execution times - 0 seconds vs 5 seconds and I want to understand what is causing this.

I get two identical execution plans:

enter image description here

everything seems the same, except the Filter operator - in the faster case 5 k rows are returned against 1.8 k rows for the the other case.

I have compare the reads of the two queries and it seems the second one is performing a lot of LOB reads:

enter image description here

I am wondering what could cause such LOB reads when the plan is the same and the data size and rows of each operator is the same (except the Filter one)?

There is another difference in the Clustered Index Seek(Spatial) operator - the Number of Executions of the faster query is 1142 and for the slower is 1180.

The Warnings of the Clustered Index Seek (Spatial) operator is Columns With No Statistics. The Column Reference are:


execution plan - slow query: http://dox.bg/files/dw?a=c133c2a7fb

execution plan - fast query: http://dox.bg/files/dw?a=24818510fb

The queries are executed on the same hardware.

  • btw, I assume the two users run against identical hardware, correct? Jul 25, 2016 at 14:55
  • Can you use sys.dm_exec_query_profiles or the "live query statistics" feature in SSMS 2016 against your 2014 server to identify the operator that is doing more work in the slower case? Jul 27, 2016 at 7:42

2 Answers 2


Comparing the two plans reveal this snippet:

                        <SpillToTempDb SpillLevel="1" />

The two plans enter execution with the same memory grant, but one has actual rows 3910 (and spills) while the other has 1502. As far as I can tell, the difference comes from the spatial operator itself. So ultimately the difference in runtime you see comes from the actual spatial data. I can also see that the actual Point you're computing the distance from differs:

          <ColumnReference Column="@Location" ParameterRuntimeValue="POINT (-75.549 39.7366)" />
          <ColumnReference Column="@Location" ParameterRuntimeValue="POINT (-83.6026 41.6721)" />

The plan that spills and has more rows to process is the one you labeled 'FAST'. Are you sure you did not mislabeled them? It makes a lot more sense for the spilling one to be slower.


A spatial index can be created only on the geometry or geography data type.it means your location data type must one of these.

Is both user run this query at same box or different location ?

Both user has different location it means index seek searching(B+ tree) the location of individual user will meet at different Deepest-Cell.

Compare with two execution plan are giving different logical read because both are scanning different parameter value for index seek.when where condition comparing different @location because each user has a different @location ,it will be also reason for different logical reads.

Spatial Index Working Details... https://www.simple-talk.com/sql/t-sql-programming/sql-server-spatial-indexes/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.