Trying to see how I can improve the performance of this Sql query

Question

I've got a SQL query which is trying to find all the Neighbourhoods in some Counties.

When I use SQL Sentry Plan Explorer to visualize the query (IMO, a bit better than the tools provided with MS SSMS) it highlights a really slow performing part :-

Full Plan

Zoomed in ....

Details

SQL script:

 -- Update which Neighbourhoods are in these Counties.
INSERT INTO @NeighbourhoodCounties (NeighbourhoodId, CountyId)
 SELECT SubQuery.NeighbourhoodId, SubQuery.CountyId
 FROM (
  SELECT e.LocationId AS NeighbourhoodId, b.LocationId AS CountyId, 
      c.OriginalBoundary.STArea() AS CountyArea,
      c.OriginalBoundary.STIntersection(d.OriginalBoundary).STArea() AS IntersectionArea
  FROM @CountyIds a
   INNER JOIN [dbo].[Counties] b ON a.Id = b.LocationId
   INNER JOIN [dbo].[GeographyBoundaries] c ON b.LocationId = c.LocationId
   INNER JOIN [dbo].[GeographyBoundaries] d ON c.OriginalBoundary.STIntersects(d.OriginalBoundary) = 1
   INNER JOIN [dbo].[Neighbourhoods] e ON d.LocationId = e.LocationId
   ) SubQuery
    WHERE (SubQuery.IntersectionArea / SubQuery.CountyArea) * 100 > 5 -- a Neighbourhood has to be 5% or more to be considered 'Inside'

Can anyone help interpret this query? What do all these numbers mean? How can I use these numbers to help diagnose and improve my query?

I tried to make an indexed view on the spatial table but that failed miserably.

Can anyone help?

Answer 1

This would be normal. You have no thick bars anywhere with mostly seeks.

However, I do see that you have a function on a column in a JOIN

ON c.OriginalBoundary.STIntersects(d.OriginalBoundary) = 1

This won't help. And a computed column won't help either

And you also have a calculation on columns too in a WHERE = non-sargable

(SubQuery.IntersectionArea / SubQuery.CountyArea) * 100

On the face of it, the 64.5% seek is probably a consequence of these JOINs and the optimiser working around them