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

[JakeSkinner]

What version of SDE are you running? i.e. 10.2.2

As a test, can you copy data from the GEOMETRY storage type to SDEBINARY?  Is the performance just as slow?

[AaronKreag]

Our previous production system was Version 10.0 service pack 5.  The new one is 10.2.2.  Your note made me think.  In our previous version we were running sde schema with direct connect.  This new version is all DBO with direct connect.  I wouldn't think that would matter.  Let me look into that geometry vs sdebinary.  Standby.

[AaronKreag]

Jake-

I created two new test databases.  In one I copied data and used the Geometry config and in the other I used the SDEbinary config.  My Geography data type test was a bust because of the datum/coordinate system issue.

A side by side test of just load speeds and in an edit session show they perform the same.

Thank you

Aaron

[DavidColey]

Hi Aaron-

We have found that hyperthreading on the CPU must be disabled whether running SQL2008 or SQL2012 when using sql geometry, but not when using binary storage. We lost nearly half of our performance until disabled.  We also found immediatly that spatial indices matter greatly with sql geometry, especially for complex polygon datasets like a parcels layer. 

The default grid levels(med x 4) and cells per object (16) did not perform well for us at all.  After disabling hyperthreading, we set up our dbTune with index levels of (med, med, med, low) with 64 cells per object.  We arrived at this level through trial and error as there still doesn't seem to be any effective guidelines- performance has been acceptable since.

I had started a similar thread here, SQL Server 2012 and 2008: Geometry Performance - that asks if there are any performance gains when moving to 2012.

SQL Server 2012 and 2008:   Geometry Performancehttps://community.esri.com/message/96614#96614‌

It's been viewed just a scant 23 times with 0 replies, although this post appears to have done just that-

Thanks

David

[AaronKreag]

Ok.  So we spent an entire day and night testing.  4 SQL servers. All same OS, Windows 2012 64 bit. same processor, same ram, same data.

1. Sql 2012 Geometry

2. Sql 2012 SDE Binary

3. Sql 2008 R2 Geometry

4. Sql 2008 R2 SDE Binary.

The results were Option 1, 2, and 3 were major fails. Option 4, sql 08R2 with SDE Binary was 2-4 times faster than any of the others, especially when in an edit session.  The snapping tool was almost instantaneous in option 4.  It was horrible, I mean horrible in 1,2, and 3.  We will be converting 1Tb of data back to Windows 2012, 64 bit, SQL 2008 R2 SP2 64bit, and SDE 10.2.2 in the very near future.

Converting to SDE Binary from Geometry in SQL 2012 did nothing.  Disabling hyperthreading wasn't even an option.

Please ESRI tell us when this mess is squared away and we can migrate hassle free.

Cheers!

[Anonymous User]

We just upgraded our SDE software and spatial Sequel Server databases to 10.2.2 which included converting the geometry configuration for those databases from SDE BINARY to GEOMETRY.  We have been having horrible performance issues while in and out of editing sessions including the snapping not working or taking so long that it appears to not be working that you mentioned.  We are still using Sequel Server 2008R2.  Do you think the change from SDE Binary to Geometry is really the culprit?  Did you ever get your system back to an acceptable performance level?

[AaronKreag]

Becky,

YES! This is the exact same behavior we had….

We actually created two different SQL servers and ran one in Geometry and one in SDE Binary and the difference is night and day. We use the SDE Binary for editing and then replicate up using a python script to the other sql server running geometry data type for our web map application. The version of SQL server does not matter. 10.2.2. does not like Geometry data type when editing. ESRI needs to get on the ball.

Aaron

[Aaron_Kreag]

Becky-

ESRI was asking us some questions from a bug report I filed so we went back to test everything again.  It turns out that the SDE - Geometry data that was originally slow is now working fine in an edit session.  This data has persisted in the database and has been used by our web map for a little while now.  So some theories are that it was related to windows updates, or  SQL is somehow becoming more efficient overtime, or we had much more load on the data sets previously when tested.

One thing that we did was, we created brand new data sets in the various configurations and then edited.  The editing performance is horrible when the data is newly created.  This is even after all of the normal data maintenance and optimization routines have been performed.

So this is leading me to believe that SQL is automatically doing something to the data over time that improves its performance.  I am not a SQL engineer so this is a bit out of my area of expertise.  Its definitely something worth looking into.

Any luck on your end?

[Anonymous User]

our IT and dba folks looked at the hardware, memory, and database performance and saw nothing that was out of the ordinary.  But like you, we saw an increased performance over time (about a weeks worth of editing).  It seemed to 'fix' itself.  or at least our IT and dba said they did not do anything and neither did I and our editing performance is back to normal.  Mystery...thanks for the update!