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

[nishadwijesekara]

Neil:

I’ve got bunch of correspondence back and forth with ESRI and implemented many tests, so the information I passed were based on all of that. But I’m afraid I’m not sure if I can share the correspondence I had with ESRI on this forum as it involves many corporate information. But I have pretty much indicated the summary came out of that discussion. I cannot make any recommendations, you will have to test this yourself and see, if your plan is to use native Geometry with SQL server 2012 R2 PS2, you will have to disable hyper-threading and implement specific spatial indexing. I couldn’t go down that path as we could not disable hyper-threading.

Nishad Wijesekara

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

ALTALINK

http://www.altalink.ca/

[BrianLeung]

Hi Rebecca,

We are running GISServer (SDE) 10.2.1 on SQLServer 2008r2 and SQLServer 2012. Our clients are running ArcMap 10.2.2. I have found that tuning sqlserver and our databases is absolutely necessary as the default settings do yield acceptable performance.

Brian

[PhuNguyen]

Hi Brian,

We have the same set up. Can you email me?

nguyenphugis@gmail.com

Thanks

[AlinaTaus]

We have done some more testing on our end and I am beginning to think that we are experiencing a different issue than some of you. One of the things we tested was to install ArcGIS 10.3 on the same machine where the SQL Server 2012 is installed in order to see if a local database connection would make any difference.  Well, it did. Everything is performing great, the features are loading super fast.  Based on this, the ESRI Tech concluded that it must be a network issue. Has anyone else run into this? I have created exception rules in the firewall for port 1433 and the sqlbrowser.exe but that didn't work. I even disabled the firewall altogether but that didn't change anything. I will ask our IT Admin about this but thought I'd ask here as well....

Thank you!

Alina

[Aaron_Kreag]

Alina,

Dumb question but are your ESRI servers in an offsite colo and you are usng like a RDP or virtual desktop?

[AlinaTaus]

Aaron,

Not a dumb question

The SQL Server is on a virtual machine on a server here at our office. The client machines that I'm using to connect to the database are not virtual, just regular desktops on the same network.

[Aaron_Kreag]

The way I understand ESRI software / data at the more current versions is that its a bit more "busy" on the network, especially in an edit session its constantly pinging the DB more than it did a couple versions ago.  Based on what you are saying, network is probably the culprit but I am not a networking guy.  I am sure there are concerns with the NIC both on the virtual host and on the VM itself...not sure how that's configured, the other issue might be the internal network switch and how that's set up / configured.

[AlinaTaus]

Yeah, unfortunately I don't know much about how the VM was set up. This might be dumb, but my reasoning as to why the culprit is not the network is the fact that I have no problem connecting and querying tables in my geodatabse when I connect to the SQL instance through SQL Server Management Studio which is installed on several desktops on our network...

[Aaron_Kreag]

Just noticed a significant performance difference yesterday when editing features in a feature data set, with topology over just features in the DB, when data was in data set it was much more degraded.  Also found a vague piece online regarding different editing speeds when using database authentication vs windows authentication with the windows authentication SDE connection performing better.  Too many different variations to test!

[AlinaTaus]

Well, we have finally found the cause of our problem. It was a network related connection and had nothing to do with SQL. Our IT Admin had to make some changes to the Network Adapter on the virtual machine, I believe the Hardware Acceleration option had to be checked off.  I have step-by-step notes if anyone else is dealing with a similar environment.  We are running Windows Server 2012 R2 and Hyper-V 2012.