New spatial query hint when using indexing

OK, last one for the evening, its getting late in Berlin. And BTW, we might have some Denali goodness here at TechEd. Search for "futures", perhaps.


There a new hint for spatial queries in Denali CTP1, namely, SPATIAL_WINDOW_MAX_CELLS. This is a table hint that, as far as I can tell, this hint affects the query sample (ie, the "other side" of the STIntersects, for example) rather than affecting the spatial index itself. If you run this example (from the census database we used with SQL Jumpstart, else substitute your own query that uses a spatial index), you can see this in the query plan.

select * from zipcodes z with(SPATIAL_WINDOW_MAX_CELLS=1536)
join Congress c
on z.shapegeog.STIntersects(c.shapegeog)=1
where c.id = 116
 
1.       Run the query with "include actual execution plan" ON.
2.       Left-Click  on the step at the extreme right, that is, "Table-valued function, Get_Geography_Tessellation_Varbinary" to select it.
3.       Hit F4 to bring up the properties window (it’s also available on the View menu)
4.       Click on the line "Parameter List", second to the bottom. Click the three dots to being up a window with parameters.
5.       Without the hint, the value of the 2-nd last parameter is 1024 (max cells that they use to tessellation the other side (in the case, the congress table) to match to zipcode table (spatial index side)
6.       With the hint (of 1536), the value of the 2-nd last parameter is 1536
 
The default value is 1024 for both geography and geometry. That’s at odds with the Denali spatial whitepaper that claims "The default value is 512 for geometry and 768 for geography".
 
Also, note the variant that uses the table hint as a query hint, this one:
select * from zipcodes z
join Congress c
on z.shapegeog.STIntersects(c.shapegeog)=1
where c.id = 116
OPTION (TABLE HINT(z, SPATIAL_WINDOW_MAX_CELLS=1536))
 
This executes without syntax errors, but doesn’t use the hint. As it should, see http://technet.microsoft.com/en-us/library/ms181714.aspx . Look for "Using table hints as query hints" and see the examples at the bottom of the BOL page, where you would use this in a plan guide (which is the whole point. Being able to use it in a plan guide, for vendor-generated code, for example, might be a big deal.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.