This query is very slow. I am new to spatial indexes and am not sure where to begin tuning this query.
With just a short glance at the plan, I'd suspect the spatial index definition, either the definition of the bounding box or the density/cells per object parameters. What does the definition of the …BlockGroupGeom spatial index look like? If, for example, its for census block groups (I have a DB of them too) intersecting a table of points (point-in-polygon query), density of high at every level is a good density for spatial index. The default is medium at every level.
A good spatial index will eliminate over 90% of the rows in the table, but the number of rows after the spatial index step is over 200,000. That's going to slow any spatial query down. How many total rows are in the block group table?
You can run the system spatial diagnostic procedures (sp_help_spatial_geometry_index_xml and friends) to see how good your spatial index is. I wrote a 6-7 article blog series of these starting here. http://www.sqlskills.com/blogs/bobb/spatial-index-diagnostic-procs-intro/.
I've run spatial queries against large tables on a relatively small VM with subsecond response much of the time. Although the small VM IS causing your Hash aggregate overflow warning, which is further slowing your query down, the >200,000 rows that are input to that step is the biggest culprit. Especially when the query only returns 1 row.
Because the definition of the spatial index parameters can greatly affect performance, this is one place where a query plan by itself is not enough. More information is needed to analyze the problem, because the shape of all plans of queries that use the spatial index are mostly the same, at least the spatial intersection part is.
Feel free to write back (or mail me directly) if you still have problems or need help in deciphering the output of spatial analysis procs.
I got a bit of input on this type of plan from Bob Beauchemin of SQLskills. I noticed there are three warnings in the plan:
- spatial guess
- missing column stats
- tempdb spill
The first two are not anything you should worry about, since there's nothing you can do that will fix anything:
- The spatial guess warning always happens (bug reported here).
- The missing column stats warning is "by design" – it always happens on the internal table for the spatial index, and isn't considered a bug.
Outside of improvements to the actual spatial index or the logic that requires the query to use it, that leaves the tempdb spill, which may indicate a shortcoming in memory to handle the query. I am out of my depth here as I have not used spatial indexes, but I hope to get more feedback from Bob on your specific plan (or maybe even coerce him to post a reply :-)).
In the meantime, I could probably make a case that Plan Explorer should suppress the missing column stats warning (the spatial guess warning, I presume, will be fixed in the near future).
Thanks for your reply. The server I am running this query on is a very small VM. I will run that same query on a beefier machine and see what happens. It sounds like to me that I may not be able to improve the performance much. We ran this same query on Postgres database and it ran substantially faster. This upset me greatly so I had to see if I could improve performance, but quickly realized I was in over my head.