I am investigating an SQL Server 2008 performance issue with ArcGIS and 'Enterprise geodatabases'. I create an enterprise geodatabase, create an sdebinary feature class for the linears, then import the features into it. There are roughly 600k linear roads in this particular feature class. After all the features are imported, I am migrating the storage engine to GEOMETRY. When I add the features to ArcMap, I observe a full refresh taking roughly 25 seconds. Within management studio, if I profile the particular statement that is being prepared and executed, I see that it is taking 70% of its time in a Filter and 25 seconds also within management studio. It's an incredibly simple query, something similar to this ' select shape from roads where filter() = 1'. I went ahead and added a hint to the query for the spatial index and the time to obtain the query results for the same statements was 8 seconds.
All fingers point to the spatial index not being utilized by the query planner.
