SQL Server 2008 Poor Performance - Spatial Index Not Being Used

JosephArmbruster — Thu, 27 Jun 2013 11:59:27 GMT

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.

Both of these have led me no-where:
http://support.esri.com/en/knowledgebase/techarticles/detail/36617
http://support.esri.com/es/knowledgebase/techarticles/detail/38871
(we are using 10.52.4266 which is > all the versions mentioned here)

I am still investigating the issue, any advice or suggestions are appreciated.

topic SQL Server 2008 Poor Performance - Spatial Index Not Being Used in ArcGIS Enterprise Questions

SQL Server 2008 Poor Performance - Spatial Index Not Being Used