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

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?

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