Unfortunately, I can't. My systems are on a closed network. What I can tell you is that the query is the same as what you'd get by adding your feature class to ArcMap and doing something simple like panning.
Well, I guess the query is small enough to just enter by hand, so here goes:
select SHAPE, TYPE, <DB_NAME>.dbo.SDE_GEOMETRY203.CAD
from <DB_NAME>.dbo.<BusinessTable> LEFT JOIN <<DB_NAME>.dbo.SDE_GEOMETRY203 on <DB_NAME>.dbo.SDE_GEOMETRY203.GEOMETRY_ID = <DB_NAME>.dbo.<BusinessTable>.OBJECTID
WHERE <BusinessTable>.SHAPE.Filter(<Long Hex Number>) = 1
So there you have it. The execution plan for this query on the prod server shows it doing a scan of the clustered index. On the dev server, it makes use of the spatial index. As I said before, if I drop the spatial and create it with a grid size of medium, it does use the spatial index.
And yes, I understand that the optimizer will choose what it thinks is the best index based on cost (taking into account statistics, etc.). I just don't understand why it might do this for two identical systems, freshly loaded data and all. I guess there has to be SOME difference there somewhere, but nothing terribly obvious. I've checked SQL Server software versions, database versions (both 110), DB settings and so on. The only thing I can think of that might be different is storage, but the optimizer shouldn't care about that. If I was getting the same execution plan on both and one was performing horribly, I could then look at storage.
I suppose I can just adjust the keywords such that it always uses a medium gridsize. I'm not too familiar with doing that. Is there a good write-up on adding keyword/parameter values with some examples?
