Poor performance with SDE on SQL Server 2012

[AaronKreag]

We recently completed a major upgrade and migration.  Our SDE went from Windows 2008 with SQL Server 2008R2 and we migrated to Windows 2012 and SQL Server 2012 (all new is 64bit).  We have the exact same settings, using a better and newer server even, the data is the same, server and sql and sde configs all the same, direct connect all the way around on all servers.

However, we immediately noticed that the SDE on SQL 2012 is HORRIBLE.  The desktop editing process lags and hangs, its like working through Citrix in 1994.  The web map application load times went from 1-3 seconds to 10-40 seconds depending on load.

Same RAM, Same CPU.... the only difference is we went to SQL 2012.

There was a similar bug reported for 10.1 here NIM082657 - When working with an SQL Server 2012 geodatabase a..‌

It appears this isn't actually fixed.  If someone has any experience with this scenario please comment and reach out to me.  Thanks!!!  Aaron

[ZacharyHart]

I know this is probably something you've already done, but we use the Native GEOMETRY storage type for our GDB's in SQL 2012 RDBMS and rebuilding the indexes is critical to performance. 

[AaronKreag]

Zach

Rebuilding spatial index or attribute indexes? According to Microsoft and ESRI Sql2012 natively does the spatial index. Are you overwriting it?

Aaron

Sent via the Samsung Galaxy Note® 3, an AT&T 4G LTE smartphone

[ZacharyHart]

Both...I have it scripted to do weekly along with a compress for our replicas. While our database is so very much smaller than yours, we find it critical to performance, especially with editing. Have you contacted the Geodata team? IMO, they are among the best ESRI Technical Support has to offer.

[DavidColey]

Correct.  When using SQL Geometry there is nothing to rebuild.  You can only reset levels and cells per level.  We found for dense, complex polygon data sets wiht a lot of varying areas or dense streets datasetw with lots of varying lengths that a index of Medium, Medium, Medium, Low with 64 cells per object greatly outperforms the defeault setting of 4 Medium and 16 cells per object when using both SQL2008 and 2012

[KentuckyDGI]

We just upgraded to SQL2012R2 with ArcSDE 10.3 and we see exactly the same performance as you outline above. We too tried all those options.

[AbhishekSingh]

Hi Aaron,

I see that you have mentioned about the bug - NIM082657..

I just wanted to make sure that you are aware about a related patch for this -

ArcGIS 10.1 SP1 for SQL SERVER 2012 Support Patch

ArcGIS 10.1 SP1 for (Desktop, Engine, Server) SQL Server 2012 Support Patch | Samples and Utilities

Abhishek

[AaronKreag]

This patch would make sense but we are using 10.2.2. My assumption is that ESRI included that patch in the new release.

That said I did talk with some guys at ESRI the other day about an unrelated issue. The conversation did go to this issue and I was informed that their sql/sde engineers are aware of and have documented more than a couple issues, one of which is listed here and that they are working on trying to find the source of the problem.

Sent via the Samsung Galaxy Note® 3, an AT&T 4G LTE smartphone

[AbhishekSingh]

I totally agree with you, the newer versions must have the older issues/bugs addressed prior to its release.

I just thought of mentioning it since the bug was mentioned and the status of the bug still does not show any version on which it is fixed.

Changing the storage type from Geography/Geometry to SDE BINARY is lengthy (for data size you mentioned, using the tool Migrate Storage) but if in case you wish to switch back later back from SDE BINARY to Geography/Geometry the tool does not support that.

[JeffWard]

I was experiencing slow edit speeds with our parcel editing.  I finally recalculated the extent of our spatial index and that fixed my problem.  Link here.

[TONIFAIRBANKS]

Did you ever hear back from ESRI about a solution to the slowness problem?