Tune query that involved spatial index – Estimates are not accurate
I've recently noticed that the exection plan for one of my queries has dropped in performance.
When I looked closely it seems that the most resource intensive part of the query is one scan of a clustered columnstore index based on a previous fitering done on a spatial query. I also noticed that the query had lower estimated number of rows probably because the geography column can't have statistics and it does not know how many rows will be returned from there.
Also the STDistance < Radius function used probably is throwing a cloak over the whole SQL Plan making it unable to estimate and causing spills in tempdb. Does anyone know how I can trick the plan into making the right estimates / assumptions for my query ?
The percentages in execution plans are always estimates, used internally by the optimizer to choose between plan options. They're not a good (or even reasonable, in many cases) measure of runtime cost, sadly.
I'm not an expert with spatial indexes or spatial queries in general, but assuming the spatial side is at least moderately selective, I would look to write the query in a form that touches the fact table last. Try writing the query in that sort of textual order, and use the OPTION (FORCE ORDER) hint to force the plan to access tables in the written order. You may also find it useful to explicitly reject NULL results from STDistance. There are some optimizations that require explicit null rejection.
That's the best I can do on the basis of an anonymized plan, I'm afraid, hope it helps.