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

[AaronKreag]

According to the tech guy I spoke with a few months ago on the GeoData team he said that the engineers are aware of the problem and that it's getting looked at....

That said there is some chatter about the bypassing the default spatial indexing and building new indexes as something that improves the performance. have not had a chance to test it.

Aaron

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

[NeilShetty1]

Hi Aaron and anyone else that looks at this,

I too had the exact same issue as you guys and went through the whole gambit of tests to determine a factor.  After about 3 months and no luck I was able to solve our issue by adding a Unique Non Clustered Index to Object ID.  On each feature class we have a Spatial Index, A Clustered index on Object ID, and a Non Clustered Index on Object ID.  Our performance increased by about 10 fold and we are now seeing performance on par to or slightly faster than our 2008 environment.  The non clustered index needs to be on every spatial layer being queried in any kind of GIS activity otherwise the whole process gets hosed.  Hope this helps someone in the future!

Thanks,

Neil

[AlinaTaus]

I am experiencing the same issues as everyone else here. I am trying to migrate our SDE from SQL Server 2008 R2 Express version to SQL Server 2012 Express and upgrading to 10.3 (although I tried adding a database connection to the migrated SDE from 10.1 and I saw the same slow performance). I have tried rebuilding the spatial indexes on the entire geodatabase and I had to shut down ArcCatalog because it just hanged for 10-15 minutes. I also tried rebuilding the spatial index on an individual feature class and although that didn't crash I saw little to no improvement.

As far as the Bug that some people mentioned, according to ESRI that has been address at the 10.2 release...

Are people switching back to SQL Server 2008? Is that the general consensus...?

Neil, how would one go about adding a Unique Non Clustered Index to Object ID? 

Thank you!

Alina

[AaronKreag]

We have had an open ticket about this since August 2014.  ESRI support worked with us....I would say more than usual so I give them an A for effort but the problem persists despite a whole laundry list of trials, scripts, and changes.  The fact remains that SDE performance was better at SQL2008R2.  Nobody knows why.  Our hypothesis is that internal engineering changes to MS SQL Server were not adequately flushed out with spatial data.

We edit in 2008R2/SDE Binary and use scripts to push to 2012/Geometry for publishing.

I am starting a SQL2014 test today!  Have the VM's spun up, I am going to start software install in a little bit.

Regardless of what happens I will post my findings here afterwards.  Thank you!

[AlinaTaus]

Thank you Aaron for the update.

We are a small shop here (2 people operation) so I'm trying to get this up and running as quickly as possible. I also have a case opened with ESRI and if I learn anything new I will post as well.

Thanks again!

Alina

[DavidColey]

Aaron - what do you mean 'The fact remains that SDE performance was better at SQL2008R2.'?  We have not found that to be the case at all, in the fact the opposite.  I replied earlier in this thread that tuning the spatial indices from the default Medium and 16 cells per level setting at all levels did not work well for complex polygon layers with lots of vertex and area variability. 

For these layers, we did find that index of Medium, Medium, Medium, Low with 64 cells per object greatly outperforms the default setting at both SQL2008 and 2012.

For point and polyline layers, we found no impact in draw and query times between our adjusted levels and cells per object and the default setting.

David

[KentuckyDGI]

After a long weekend of testing we have identified the root cause on our end. The slow performance experienced by our users appears to be the result of a “new” default keyword setting at ArcGIS 10.3. This setting forces all data in an MS SQL Server DB to be stored as a GEOMETRY data type rather than SDEBINARY. The databases on our old staging server that use SDEBINARY, responded instantaneously to panning and identifies, whereas the data on new production and staging servers had an 8-15 second lag time. We have tested this on 3 different clients located within different subnets on our WAN and the performance we once enjoyed was been restored.

Essentially, each time we imported updated agency data from staging database it was converting it to a GEOMETRY data type due to the new default keyword setting at 10.3. Thus, all the feature datasets that have been updated since our ArcSDE upgrade (10.0 SP5 to 10.3) a couple months ago were impacted. This is why users we experiencing performance issues with some layers and not with others.

I'm sure it is possible to "tune" an ArcSDE instance that is setup with the GEOMETRY Storage type, but if you don't know that you are using that option, it is difficult to take the appropriate actions.

Never a dull moment!!

[DavidColey]

Sure I understand.  We define all of our storage parameters upfront in our dbTune tables for location, type, page fill params, spatial index params and more and assign that to a VECTOR keyword.  Whenever we import or otherwise create data we simply use our keyword and in that way we are consistently set up across all of our user instances.  We moved away from SDEBINARY storage at 10.0, and after initial struggles isolating our performance issues (disk raid config, san vs local array, network I/O, indexing) using SQL GEOMETERY storage, found that the spatial index parameter adjustment had by far the greatest impact on our performance.

For example, our parcels layer was never super speedy even stored as sdebinary, but was initially terrible at sql geometry.  After eliminating disk R/W, network I/O, memory paging, sql system database locations (esp temp db), primary and secondary data file storage locations and transaction log settings we found that the resetting the spatial index levels and cells per object greatly increased our desktop performance when usgin SQL GEOMETERY.

David

[George_Thompson]

Hi Kentucky DGI and David,

I wanted to clarify that SQL Server Geometry storage became the DEFAULT stating at the 10.1 release and not at 10.3.

When you create a new geodatabase in SQL Server or upgrade an existing geodatabase, the default geometry storage type used for new feature classes is SQL Server geometry. Therefore, all newly created feature classes can be accessed through SQL.

If you do not want new feature classes to use SQL Server geometry storage by default, you can use the sdedbtune ArcSDE administration command to change the GEOMETRY_STORAGE setting under the DEFAULTS keyword in the SDE_dbtune table. You can install the ArcSDE administration commands using the ArcSDE Application Server for SQL Server installation wizard.

This is from the What's New for Geodatabases at 10.1, under the section: Default geometry storage type changed

Hope this clarifies when the DEFAULT geometry storage type for SQL Server changed.

-George

[DavidColey]

That's fine George, I was not unclear.  We purposefully moved to SQL Geometry at 10.0.  I am simply trying to explain to the community that spatial index levels and number of cells per level does have a performance impact.  We were able to solve that impact by tuning our levels and counts and I am just trying to pass that info along.

David