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

[ThomasColson]

When using Geometry as the storage format, there are significant performance differences in spatial index parameters. Witch Magic, Snake Oil Medicine, and Spatial Index Tuning might help you here.

[heatherlizama1]

Hi Alina,

Could you please send me the step by step document on the Hardware Acceleration option? re/ "some changes to the Network Adapter on the virtual machine". We have the same set up.

heatherlizama at polargeomatics dot com

Thank you in advance!

Heather

[BrandonVan_Horn]

Can you email your procedure to please. brandon.van.horn@yavapai.us. Thanks

[BrianLeung]

Hi Brandon,

I haven't got this completely documented but I do have something that I can give you:

Configuration GISServer (SDE) and MS SQLServer:

I haven't got this completely documented but I do have something that I can give you:

Configuration GISServer (SDE) and MS SQLServer:

1.

Maximum Degree of Parallelism - MAXDOP

The default value for MAXDOP is 0 (zero) and can be set or

viewed using (sp_configure). A value of 0 means that SQL Server will use all

processors if a query runs in parallel.

If Hyper-Threading is enabled, MAXDOP should not be 0 and

should not be greater than half the number of visible schedulers.

A SQL Server with two CPU sockets and a 4 core CPU that does

hyperthreading, the maximum degree of parallelism option should be set to 4.

Note: The max degree of parallelism configuration option

does not limit the number of processors that SQL Server uses. To configure the

number of processors that SQL Server uses, use the affinity mask configuration

option.

Note: Non-uniform Memory Access nodes (NUMA) is mentioned

many times. Virtual servers may not have this configured. If your server does

have NUMA nodes then you should be aware of the number of cores (with or

without hyperthreading) per node.

I haven't got this completely documented but I do have something that I can give you:

Configuration GISServer (SDE) and MS SQLServer:

1.

Threshold for Parallelism

Recommend starting with this setting at 50. The default is set to 5 which is a

really low setting. Make sure you measure for

the critical queries and tuning up or down as appropriate

I haven't got this completely documented but I do have something that I can give you:

Configuration GISServer (SDE) and MS SQLServer:

1.

Spatial Index setting:

The default spatial index setting is too low. Grids = (Medium,

Medium, Medium, Medium) with cells per object set to 16. It has been

recommended to set the cells per object to 64 with Grids = (Medium, Medium, Medium,

Low). You can set this per feature class or you can change the default setting

settings using sdedbtune:

sdedbtune –o alter –k DEFAULTS –P A_MS_SPINDEX –v

"GRIDS = (MEDIUM, MEDIUM, MEDIUM, LOW), CELLS_PER_OBJECT = 64" -i {server

instance} -D -u -p

Note: I have noticed some quirks with the resulting spatial index

using the geoprocessing tool. I recommend using the Feature Class wizard (in ArcCatalog

select properties for a specific feature class) when adjusting the spatial

Index for a specific feature class.

I haven't got this completely documented but I do have something that I can give you:

Configuration GISServer (SDE) and MS SQLServer:

1.

Spatial Statistics:

Make sure you rebuild the spatial statistics after

rebuilding your indexes. It is also good idea to rebuild the spatial statistics

of a feature class after a large data load.

Ref:

ESRI GeoNet - https://community.esri.com/thread/106931

Jeremiah Peschka – SQL Server Settings – http://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/

Microsoft - MAXDOP Settings- https://support.microsoft.com/en-us/kb/2806535

Stack Exchange – MAXDOP setting - http://dba.stackexchange.com/questions/36522/what-is-a-good-repeatable-way-to-calculate-maxdop-on-sql-server

Alastair Aitchison (a good video on how spatial indexes

work) - https://sqlbits.com/Sessions/Event5/Creating_High_Performance_Spatial_Databases

Cheers,

Brian Leung

[jorisfrenkel]

Two comments on this:

1. According to the documentation, the parameter A_MS_SPINDEX is used for versioned feature classes only. For non-versioned feature classes, the parameter B_MS_SPINDEX is used. B_MS_SPINDEX  can be set to the same value.

See:

ArcGIS Help 10.1

2. Setting these parameters is not possible anymore in ArcGIS 10.3.1. I asked a question about this to ESRI support, and they confirmed this.

The properties of the spatial index can be changed through SQL Server Management Studio. ESRI support tested this for me, setting the levels to High,medium,medium,medium, and also tested Medium,medium,medium,low with 64 Cells_per_object, as recommended in this forum, but performance deteriorated. So it seems that at SQL Server 2014 and SDE 10.3.1 the default spatial index settings (geometry autogrid) perform the best.

I also compared Geometry and Sdebinary formats. They do not differ too much in drawing times, except when drawing feature classes with a single symbol legend. Then Sdebinary significantly outperforms Geometry (2 to 3 times faster), especially with large datasets. I didn't test performance while editing.

Joris Frenkel

Staatsbosbeheer, the Netherlands

[nishadwijesekara]

We are going to setup ArcSDE 10.3.1 with SQL server 2012 R2 on Windows 2012 R2 environment. We experienced the same issue. However, SQL server 2012 with SDE binary (original geo-databases were on 10.0 with SDE binary and are upgraded to 10.3.1 with SDE binary) seems promising.

Did disabling hyperhreading and applying https://support.microsoft.com/en-us/kb/2896720 resolve the issue of having SDE database with SQL server native Geometry type data ?

[BrianLeung]

I found the following helpful as well as the above mentioned spatial index levels.

http://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/

[JerryGarcia]

We just migrated from 10.2.1 to 10.3.

We are experiencing very slow GDB connection times in 10.3 relative to 10.2.1.

For example, the amount of time it takes to start up a map service from the stopped state is ~3 minutes; at 10.2.1, it was ~20-45 seconds.

This is too big of a degradation in performance for us.  Due to security requirements, we manage services dynamically, i.e. turn on/off based on usage.  We need services to turn on quicker than ~3 minutes. 

We are using SQL Server 2012 GDB.

What are the steps to trouble shoot this problem?

Are others experiencing performance degradation with database connection via ArcGIS with 10.3?

Thanks!

[nishadwijesekara]

This is a common problem Jerry as you can see in this forum and is a known issue acknowledged by ESRI. You are probably using SQL Server native Geometry type data on SQL Server 2012 connecting to ArcGIS 10.3.1 using SQL server native drivers which causes this problem.

If the above setup is true for you, either of the following should work:

- try to use SQL server 2014 with ODBC drivers

- change all SQL server native Geometry/Geography types back to SDE binary

- fine tune or create new SQL Server spatial indexes with Geometry type data (a brief procedure of that should be available in this forum)

[WilliamRice]

I have also encountered poor draw performance with certain feature classes that were loaded into SQL Server using the Microsoft GEOMETRY format.  This poor performance was most evident with our Parcel polygon feature class where a large CPU spike of 50% to 60% would occur on the server with one simple pan of the feature class in ArcMap.  This poor performance was first encountered with a SQL Server Geodatabase upgraded to 10.3 with SQL Server 2008 R2 and was not improved after upgrading to SQL Server 2012.

After much effort, I was able to get a bug created for this issue.

BUG-000086937 : Panning/Zooming in ArcMap creates larger spike in CPU usage for Sql Server Geometry data compared to SDEBINARY.

The reference Esri Case# for this bug is "Esri Case #01618154  - SDEBINARY vs. SQL Server Geometry performance concerns".

Another issue for me with the 10.3 release is that Spatial Views cannot be created with the ArcCatalog "Create New View" command if the Storage format is SDEBINARY.  The case number for this issue is the following:

Esri Case #01610572  - Spatial view creation not available in 10.3? 

Because of the poor and unusable draw performance with ArcGIS 10.3 and the SQL Server GEOMETRY format and the Spatial View creation issue, my work around for this issue for the forseeable future is just to keep our SQL Server Geodatabase at version 10.2.