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]

David,

A couple thoughts.  Unfortunately your suggestion (as well as others) did not solve our problem, nor did ESRI solve the problem.  Have you submitted your solution to ESRI?  While I appreciate your feedback / input, the fact is that our geometry data type on SQL 2012 has been and continues to be a disaster.  By simply moving the same data back to SQL 2008R2 the problem simply disappears and it doesn't matter if the data is in SDEbinary or Geometry.  Hence my comment.  Second, there is an expectation by the user community that the compatibility, performance and functionality of the approved versions of ESRI and SQL Server work...out of the box, without the need for experimenting with settings.  If we are going down that path, we may as well just go back to Server / SDE at 9.1, and IMS.  I think the great majority of folks that are hitting these forums, looking for answers are the same people that are feeling their way through an implementation or migration.  My posting here is probably more a cautionary tale than anything else.  I am still wondering if SQL 2014 is any better out of the box and expect to test that soon.

[DavidColey]

Really?  Wow that's tough Aaron, I now see where you're coming from.. I've asked ESRI on several occasions to chime in on spatial index tuning params when using sql geometry with no feedback. I don't think they know.  Perhaps Vince Angelo's Blog can chime in here.  For us, we did find that our spatial index tuning did solve draw and snapping problems on our larger poly layers, and (again for us) sql 2012 handles sql gemoetry better than sql 2008.  I don't know why: a columnstore index on the geometry column that didn't exist at 2008?  But if that were the case wouldn't things work better for you?

David

[VinceAngelo]

I think you're overestimating Esri's control of this situation.  The only choice Esri made was to make native geometry the default storage option (and even this wasn't much of a choice, since the native geometry solution should be better than the complex joins required to SDEBINARY storage implementation).  It's disappointing that GEOMETRY performance appears to be so sensitive to data content, but it's Microsoft's implementation, and Esri has limited influence on any changes.

I can't pretend to be an expert in SQL-Server tuning, and I doubt many at Esri can.  Given the high variability in data composition, it's very difficult to present even a one-paragraph tuning recommendation without 3-5 pages of legal back-pedaling.  In light of this, it doesn't seem outrageous that Esri staff would keep a low profile when requests for optimal solutions are made..

If Microsoft's implementation is causing vexation, you have options to address the issue: You can contact a Microsoft forum, or Microsoft Tech Support, or even evaluate other RDBMS options.  All GeoNet can offer is stories from other Esri users who have been successful (or not), and what they needed to do to get there, or more generic advice on the impact of spatial data organization which are applicable to all storage formats (utilize scale dependency in mapping, beware of too many vertices, or of row organization which is near-random with respect to draw order [aka spatial fragmentation]).  To these I'd have to add caution with respect to "out-of-the-box" solutions on any IT platform -- sometimes site-specific complications defeat generic solutions.

- V

[Aaron_Kreag]

When I read your reply on my phone my first thought was...does this guy work for ESRI?  When I logged in on the PC to reply, I could see that was the case.  What I gather from your reply is that you acknowledge there is an issue but are saying that at the end of the day, it is really Microsofts problem....

[VinceAngelo]

It's a common enough problem, which Esri shares, since we use our software for deployment at user sites, but the solutions to GEOMETRY performance can only come from Microsoft.

[Aaron_Kreag]

I have to ask.  What version of ESRI Desktop is everyone using?  It was our 10.2.2 Desktop connected to SDE with SQL2012 Geometry where we seen the major performance issue.  However I spun up a new server and installed SQL 2014 and 10.3.1, loaded data into SDE with Geometry and its lightening fast!  I think next week I will spin up a couple more test servers to check different combinations to see if I can isolate it.

Aaron

[PhuNguyen4]

Aaron,

You try updated hotfix below for sql server 2012.

https://support.microsoft.com/en-us/kb/2896720

Hope this help you.

Phu Nguyen

[Aaron_Kreag]

Yes.  We are at SP2 CU3 so this fix has been addressed.  The SP2 CU3 was actually a specific recommendation from ESRI for anyone that is interested.

[nishadwijesekara]

Aaron:

I came across the performance issue with Geometry data based on the connection between ArcGIS 10.3.1 (Server, Desktop) and SQL server 2012 SP2.

This is mainly because of the bridge they have introduced with ArcGIS and SQL server 2012 which 64 bit SQL server native client drivers. ESRI is still working on this issue with Microsoft.

ESRI used to have ODBC drivers connecting to SQL server, with SQL server 2014, they have changed the driver back to ODBC, this is why you experience it to be fast.

We are migrating from 10.0 to 10.3.1. 10.3.1 ArcGIS seems to be full of issues and I tend to relate this experience to the Windows Vista release, ☺, ESRI need to put their fullest effort to resolve all these and come up with 10.3.2 soon.

We have made our mind to keep our data as SDE binary, we used to have SDE binary with 10.0.

Everything is fast, except for stopping and starting the map services compared to 10.0. Is there any other way to further fine tune the performance, we have rebuild the indexes, re-analyzed the tables and feature classes, etc. we still have hyper-threading on, cannot turn that off, it is a major infrastructure change.

Nishad Wijesekara

GIS Specialist, ext - 8370, TP: 403-387-8370

ALTALINK

http://www.altalink.ca/

[NeilShetty1]

Nishad,

This is some great and very interesting information.  I am wondering if you have any documentation about this issue.  I would love to read up on this topic.

Also if an ESRI tech staff could confirm this that would be amazing.  As someone on the verge of doing some problem management for slow performance when migrating to SQL Server 2012 it would be nice to not waste a ton of time if this is the fundamental issue.

Thanks,

Neil Shetty