Slow drawing from SDE (SQL Server)

[JoshuaKeller]

I am attempting to setup two servers for testing purposes and am having a problem with connections to SDE. My setup is 2 internal virtual servers both running Windows Server 2008 R2. One is setup with SQL Server 2008 R2 and the other is setup using ArcGIS Server 10.1. The ArcGIS Server machine also has the SQL 2008 R2 Client Connectivity tools installed. Both servers for the test are on the same hardware using the same SAN for storage. I used the toolbox to create an enterprise GDB in SQL and then added that as a registered data store with AGS. I then exported a polygon of the states to SDE and published that as a map service so AGS was connecting to SDE for the data. That services takes about 5-10 seconds to draw no matter what the extent, which much slower than I thought it should be. I then published the data to AGS from a local FGDB (forcing it to copy the data to the AGS) and that draws the service almost instantly. As final test I copied the FGDB from my workstation to the data drive of the SQL server (same folder as the SQL .mdf for testing) and shared that, then registered the folder as a data store. I published the service again and this also draws instantly which tells me the problem is on the AGS connectivity to SQL Server (not a network issue as I removed that by putting the data in the same location).

One thing I do notice is that for the 5-10 seconds that it is attempting to draw when the data comes from SDE the AGS machine has both CPUs pegged at 100% while the SDE server looks like almost nothing is happening (no different than if I am pulling it from the FGDB).

Does anyone have any ideas on what could be causing this?

Thanks,
Josh

[VinceAngelo]

I'd always expect a local file geodatabase of a simple table with "draw all features"
to run rings around an ArcSDE database (even one run on physical hardware, and
not imprisoned on a VM). 

In order for ArcSDE to shine, you'd need 8 concurrent mapping clients to several
million rows of realtime multi-user edited enterprise data, queried from a physical
server via Direct Connect, where only a tiny fraction of the data is rendered in
each map.

-V

[JoshuaKeller]

I had expected the FGDB to be slightly faster in this circumstance, but not 5-10 seconds faster, and the odd part to me was the high CPU usage on the client (AGS) side of things with little to no activity on the SQL side. While physical hardware would be ideal it simply isn't an option we have at this point, and since I need to be able to edit features I am stuck with using SDE rather than the FGDB. Is there something I'm missing here or is this 5-10 seconds to draw a simple table to be expected?

Thanks,
Josh

I'd always expect a local file geodatabase of a simple table with "draw all features"
to run rings around an ArcSDE database (even one run on physical hardware, and
not imprisoned on a VM). 

In order for ArcSDE to shine, you'd need 8 concurrent mapping clients to several
million rows of realtime multi-user edited enterprise data, queried from a physical
server via Direct Connect, where only a tiny fraction of the data is rendered in
each map.

-V

[VinceAngelo]

Clearly, 5-10 seconds is too long, but there's too many variables to predict what should
be happening --

What kind of connection protocol are you using?  Is the user database or OS authenticated?

How many tables are visible to the user?  How many feature datasets?

What geometry storage type is being used in the layer(s)? Are the envelope(s) set correctly?
Are there spatial index(es) built on every layer? 

What's the nominal 'ping' time for traffic between the AGS and DB servers?  Have you tried
using se_toolkit utilities like 'sdeping' and 'sdequery' to capture the time required to connect
and query outside of an AGS session?  How do the feature class(es) perform from ArcMap?

How are the database file group files accessed by the database?  What kind of performance
does SQLCMD provide locally on the database VM, and from the AGS VM?

Have you tried using SDETRACE or native database auditing to capturing timing information?
Have you verified an efficient query plan?  What does the VM server logging report?

Identifying bottlenecks can sometimes be quite challenging, but multi-second delays indicate
there is a fundamental issue with your configuration.

- V

[JoshuaKeller]

First, thank you for the help. I did some more testing based on some of what you suggested, and started with SQL profiler and performance monitor. Based on the SQL profiler and the performance monitor SQL appears to be returning the shape for the entire US within 800-1000ms and watching the network the data all appears to be transferred within about a second, then the CPU on the AGS machine chunks away for 4-10 more seconds.

So based on that, it seems like it may not be a SQL issue because SQL is getting the data to AGS within 2 seconds, AGS is then taking an extra 4-10 seconds once it gets the data to render. When it pulls the data from the network share data transfer happens slightly faster as expected, and rendering barely puts a blip on the CPU monitor.

This leads me to believe that I should be focusing on the AGS server, or there is some sort of additional overhead for AGS to process the SQL data since the same exact dataset renders so much faster out of FGDB than SDE once it gets the data. If it makes any difference, SDE is using SQL geometry for the shape.

Thanks,
Josh

[VinceAngelo]

How complex are the geometries?  Did you query the Well Known Text in your SQL test?

Using 'sdequery' will give you an idea of the cost of getting the data to the render engine.

Have you tried using a Query Layer to render the same data?

- V

[JeffAzevedo]

Are your features using the Geometry spatial type? If they are, compare it against the same feature using SDE binary type. I believe you will see a huge performance boost.

*edit
I just noticed you are using Geometry Spatial Type. This was a huge problem for us across Editing, (your exact problem) snapping, measuring and queries in general.

To test this, copy a few features that are Geometry Types, and paste them into the same Dataset. Make sure the Config. Keyword is set to SDEBINARY. I'm betting all your problems go away.

Good luck.