I have a
spatial index and the following query:
SELECT DS2.[ID] ,DS2.[InstanceID] ,DS2.[Location].STDistance(@Location) 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
@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:
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:
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
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.
Warnings of the
Clustered Index Seek (Spatial) operator is
Columns With No Statistics. The
Column Reference are:
[extended_index_1523900896_384000].Attr [extended_index_1523900896_384000].pk0 [extended_index_1523900896_384000].pk1
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.
sys.dm_exec_query_profilesor 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?