SDE (SQL Server) Connection Latency

[BrianCollins]

Looking for input and/or testing ideas. We have enterprise geodatabases (SQL Server 2016) running on VMs in our data center. They vary in complexity from simple default with only a few feature classes/assets to heavily versioned with numerous feature classes/assets.

When I open the properties window for an SDE connection file on my desktop there is a noticeable lag. The time is acceptable for the simpler databases but for the complex ones it takes many minutes (5, 10, or more) and sometimes I just give up. When I open the same properties window on a VM also running in the data center for the same SDE connection using the same credentials the response is very fast. Even the complex databases open in 30-40 seconds. Same version of desktop. Same license level. Everything should be apples to apples.

One of the only factors I can attribute a difference to is network bandwidth. So how do I test this issue? Is there some tool (wireshark, fiddler, etc...) so I can see what's going on under the hood? Could network gear be throttling the communication to our remote offices? We're on high-speed connections.

Final mention...this issue can be replicated with multiple machines and users in various office locations.

[MichaelVolz]

Are you working in ArcMap 10.x or Pro 2.x?

[BrianCollins]

ArcGIS 10.6.1. I'll try ArcGIS Pro 2.2.x now for comparison.

[MichaelVolz]

Do you have the ability to upgrade Pro to a newer version?  Does the computer you are using exceed the requirements to run Pro, especially with respect to a dedicated video card?  If not, I'm thinking you will be even more disappointed with Pro's performance than ArcMap's, but it's definitely worth testing just to see for yourself.

[ChristianWells]

From your description, it sounds like the client to server distance may be the primary factor. How far is the data center relative to your client machines?

A set of tools to get you started would be an SDEIntercept (client-side) and SQL Server Trace (server-side).

SDEIntercept

https://support.esri.com/en/technical-article/000014807

SQL Server Extended Events

https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-event...

Here is a good resource to get started understanding performance within the context of the SDEIntercept:

https://support.esri.com/en/technical-article/000010355

[BrianCollins]

In terms of physical distance from the data to my client...90 miles :-). In terms of trace route hops there are five hops to reach the database host.

I've run SDEIntercept on both my local client and a VM inside the datacenter (1 hop to database host). For our most problematic/latent database connection, the start-to-finish time for the log file was just over 28 minutes. The same operation on the VM resulted in a start-to-finish time of 48 seconds. This is a HUGE discrepancy and my gut tells me that one of the devices I'm hopping through for the data to reach my client is significantly slowing down this process.

Incidentally I'm seeing similar results with ArcGIS Pro, so that doesn't solve the problem. I've also removed the native SQL Client and installed the latest SQL ODBC driver but no dice there either!

[ChristianWells]

I was still thinking about this problem and I found that 'tracert' may be a good starting point to see where the greatest lag is. It's a built-in Windows tool, so you should be able to run it from wherever ArcGIS Pro or ArcMap are installed.

https://support.microsoft.com/en-us/help/314868/how-to-use-tracert-to-troubleshoot-tcp-ip-problems-i...

[MichaelTorbett]

@BrianCollins did you ever figure out the cause of your connection latency? I'm having almost the exact same issue you had.

[MarceloMarques]

FAQ: Why is the connection slow to a SQL Server geodatabase? | Esri Australia Technical Blog (wordpr...

if you are having issues then please open a ticket with Esri Tech Support to investigate your problem further.

Caveat, database servers and client machines need to be on the same LAN - local area network for better performance, if you are trying to connect to a database server via VPN or even through a dedicated network link the database connection will be slow, the traffic needs to cross different firewalls and the network bandwidth is low and latency is high because of distance, on LANs the bandwidth is high and latency is low, therefore the database connection will be faster.

[MichaelTorbett]

Thank you @MarceloMarques .   My EGDB is in Microsoft Azure but ArcGIS Pro is on my local machine. It looks like I'll need to create a VM in Azure and install Pro there.