I'm using ArcSDE 9.3 and SQL Server 2008 R2 and I want to migrate from ArcSDE 9.3 to ArcSDE 10.1.
Is there any performance differences between ArcSDE 10.1 and ArcSDE 9.3 ?
Is there any change in tables of ArcSDE 10.1 and ArcSDE 9.3?
I'm familiar with Online Help about how to upgrade ArcSDE for SQL Server from 9.3 to 10.1 .
I couldn't find resources about performance changes in 9.3 to 10.1.
The most important thing 9.x users need to know is that ArcSDE no longer exists.
There had been no fundamental changes to the ArcSDE API since 9.0, when XML, INT64, UUID, CLOB, NSTRING and NCLOB types were added. Since then, the only changes have been Projection Engine mods to support newer projections and vertical datums. The API itself is now deprecated at 10.2.x, and will not be released with 10.3 in the fall. The table was set for this change at ArcGIS 9.2, when ArcSDE itself was no longer directly licensed.
You should not see any fundamental performance changes for like operations between ArcGIS 9.3 and ArcGIS 10.1, but you will also be hard-pressed to find like operations. The geodatabase schema tables made a fundamental change going to 10.0. This should result in significant performance improvement in initial connection time of all ArcObjects geodatabase clients. There weren't any changes to simple ArcSDE connection queries, so it's unlikely you will see significant differences there.
The other significant change for MS SQL Server users was at ArcGIS 10.1, when native GEOMETRY storage became the default storage type for enterprise geodatabase feature classes. This should have resulted in a performance improvement as well (moving away from the three-table join of SDEBINARY storage to a single table with native type), but there still exist many use cases where GEOMETRY and GEOGRAPHY are slower than SDEBINARY. You should definitely review query performance characteristics of SDEBINARY layers (with and without default <1mm coordinate precision) and GEOMETRY/GEOGRAPHY layers, and do so again when you upgrade to SQL Server 2012.
ArcGIS 10.0 also introduced Query Layers, which operate outside the ArcSDE technology stack. And ArcGIS 10.1 introduced a different look to the Direct Connect process, making it easier to put aside application server connections (which are also deprecated at 10.2). Both of these represent significant changes from ArcGIS 9.x (searching on "What's new in ArcGIS" and reading the 10.0, 10.1, and 10.2 transition documents is highly recommended).
You should know that ArcGIS 10.1 is elderly at this point. You should certainly apply Service Pack 1 and the Quality Improvement Patch (QIP), and the many post-QIP patches. ArcGIS 10.1 is set to move into "Mature" release status in January 2016; there may not be many more patches before it is retired.
