I've been looking at the spatial index stored procedures (sp_help_spatial_geography_index_xml and friends) a little harder recently, in an attempt to help answer two questions.
1. If I have a spatial query, why does/doesn't it use my spatial index?
2. If I have a specific spatial query in mind, what are the best values to choose for my spatial index density?
In addition to the diagnostic information presented in the procs, there are a few things to keep in mind when attempting to answer the first question. Currently, the query optimizer is estimating "high" when it comes to using the spatial index, so there are occasions were the spatial index should be used and isn't. Isaac mentioned this in his PDC talk, and mentioned that this would be changed in the next refreshed. As far as I could see, it isn't in SQL Server 2008 CU2.
Also, remember that SQL Server will never use cardinality estimates for a parameter set in a variable by the SET statement unless you use OPTION(RECOMPILE) on the statement. This is a general SQL Server behavior, not a spatial behavior because of when SQL Server creates the query plan. So if you want cardinality estimates for the variable @g in the query "select geog from table where geog.Intersects(@g)=1" you must either pass @g in as a stored procedure parameter or use sp_executesql. Realize that if you use a parameterized query in a client app, the client API stacks will change the parameterized query to invoke sp_executesql, so you're OK here. According to Michael Rys' SQLPASS talk, one spatial-specific behavior is that cardinality won't be used if @g if specified as a constant, that is "select…where geog.Intersects('POINT(1 1)')=1 " either. Finally SQL Server doesn't change the query plan in a parameter value changes, general behavior, not spatial-specific.
Back to the diagnostic stored procs.
The information that the spatial index procs provide consists of three main categories:
1. Information about the index itself
2. Information about the query sample
3. How efficient the query index is when used against the query sample
The query sample is specified as an input parameter to the diagnostic procs, and would be "@g" in the Intersects queries above.
If you've read my previous blog post on the diagnostic procs, read it again, I've revised it. Filters are the subject of the next post.