I am troubleshooting ArcGIS 10.1 performance issues with SQL Server 2008 R2 SP 2 (10.50.4000) and the issue appears to be this MAXDOP issue: https://connect.microsoft.com/SQLServer/feedback/details/625896/spatial-index-not-used-on-multiproc-machine-unless-maxdop-set-downward
The issue is that the query optimizer is not using the spatial index when it should. Setting a
OPTION (MAXDOP 1) query hint on a SQL statement will cause the spatial index to be used as will an index hint, but neither are an option for the Arc-clients.
I've read that SQL Server 2008 R2 SP 1 - Cumulative Update 3 and higher has the fix but I'm seeing it at SP2. And I don't see the fix listed in the documentation for any of the CUs, but I'm hoping it is undocumented and present in one of the cumulative updates. Has anyone had this issue at SP2 and fixed it with one of the SP2 cumulative updates?