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

[ScottFierro2]

I was under the impression that it wasn't until 2016 that the "In Memory" options became available because we were planning on reviewing options with using it in conjunction with column stores for indexes to evaluate upstream gains for our front end applications that are heavily queried or run full table scans often.

[SusanMcClendon1]

I just wanted to share my experience on this thread with poor performance on SQL Server 2014 SP1 and ArcGIS 10.3.1.

• Brand new development system - SQL Server 2014 SP1 (12.0.4100.1), ArcGIS 10.3.1 (patches applied thru 3/31).
• MS Windows 2012 Std HyperV architecture; Virtual = 2 logical processors / 16 gb RAM. Hyperthreading enable on host, no choice to disable.
• IMHO - MS HyperV and Hyperthreading and SQL Server 2014 is perfectly valid config, at least according to Microsoft.
• Single tier (DBMS and ArcGIS Server one box, for dev - ArcGIS Server not running yet).
• New GeoDatabase. Upon loading a 70000 polygon land track layer, performance was abysmal at 2+ minutes to draw.
• Found this post and started troubleshooting, thinking to myself no way could the SDEBINARY vs GEOMETRY issue still be a thing (I first saw this at SQL Server 2008 R2). But lo and behold, converted layer to SDEBINARY and it loaded in 20 seconds flat.
• Tried a different less complex 40000 polygon veg map. Still about 70% slower performance than SDE Binary (thinking hey maybe it is the data). There are only these two layers in the entire database.
• Hacked trough this post https://community.esri.com/blogs/HackingArcSDE/2015/07/07/witch-magic-snake-oil-medicine-and-spatial..., and many of Alastair's posts, and others. Learned as much about SQL spatial indexes as I used to know about Oracle spatial indexes back in the day. Nothing improved performance, not the optimal CELL SIZE or tessellation scheme or grid levels (HHML).
• Went thru all cumulative updates to SQL Server 2014 post SP1 - nothing new on spatial indexes. SP1 contains fixes from MS https://support.microsoft.com/en-us/kb/288788 , https://support.microsoft.com/en-us/kb/2887899​  (need to double check now on any updates related to HyperV/Hyperthreading).
• Swapped back to ODBC SQL 11 driver - at first it helped a little (under 1 minute draw), but a day later, performance still tanked.
• Tried setting Max Degree of Parallelism, and increase Cost Threshold for Parallelism (2 / 50). No improvement.
• Installed Desktop Client on Server - tested from there (network? grasping at straws?). No improvement.
• Checked with IT guy - 1 LUN for all virt disks (no choice at this time, he inherited a big array of disks - we already planned to remedy this before production launch); Fixed Virtual Disks; 4k block size. Not sure if we will try to set to 64k, IT guy leery that will really help issue.
• We can't find specifics on changing hardware acceleration (do you turn off virtual machine queue or IPsec task offloading or both?? - not really a fan of shooting in the dark.) These are HyperV defaults.
• Virt Memory is Static - we are going to test changing that to dynamic per http://sqlmag.com/sql-server/sql-server-virtualization-tips

I have used ESRI products since the start of my career and like many have often had a love/hate relationship. Today the hate is strong. This should not be this difficult. Unfortunately we are not authorized to contact ESRI support directly at this time. Right now I'm considering scrapping and trying to get an exception thru to try PostGRE/GIS.

Cheers (PC Load Letter)

Susan

[SusanMcclendon]

I just wanted to update my experience in this thread. We upgraded our test environment enterprise geodatabase to 10.4.1 SDE and the performance issues we had on our large land trac and vegetation layers disappeared. This was true for our 10.3.1 clients and upgaded 10.4.1 test client. That'll teach me for not going with the latest version (but 10.4 came out the week we started this project)!

For a test, I loaded the layers fresh (no spatial index tuning), and they were fast. I also backed out SQL server level changes and we  made no changes to the underlying disk infrastructure. Unfortunately, due to the delay that trouble-shooting the performance issues caused, the project was scrapped for now.

My best guess, the issue for us was with the rendering engine or how the results of the query were fetched from the server. Something changed between 10.3 and 10.4. I think that ArcGIS was fetching the results in batches at 10.3 to render, which for some reason was abysmally slow, but at 10.4 it fetched all results then rendered. I base this in part on our testing  with QGIS, which rendered the data fine connected to SQL Server, and near as I could tell fetched the entire dataset at once. I'm not able to prove this theory, if someone can, I would be interested in the results.

Susan

[Yann-EricBoyeau]

Hi Susan,

You should really consider increasing the HDD Block size to 64K.

In my experience, this change was a winner in 3 differents organizations.

Still not reaching SDEBINARY performance but good enough;

You will find many references to this on the web, like : How to improve SQL Server's IO performance by up to 40%  or Disk Partition Alignment: It Still Matters–DPA for Windows Server 2012, SQL Server 2012, and SQL Ser...

www.MichaelSteineke.com | Block Size and Performance with Hyper-V and SQL Server

[DavidColey]

Hi all-

we just completed our production migration to windows server 2012 r2 running sqlserver 2014.  For the server architecture, we increased our bytes per cluster (for all drive sectors in the RAID) to 64K, and we left hyperthreading (no more gsrv to worry about) enabled.  Same memory as our sever 2008 r2 box, 64gb but now 16 logical processors as opposed to the physical 8.  We updated all the sde instances to 10.4 and I must say we are quite pleased.  Editing has been going really well, snapping is working really well, draw times are very fast.  The autogrid spatial indices are behaving really well.  Connections are faster. 

David

[PhuNguyen]

Hi David Coley,

Are you using direct connect to Enterprise Geodatabase in SQL Server 2014? Sql native client or odbc 11 driver? Geometric Network Model?

Can you help me step by step drive configuration?

Thanks.

[DavidColey]

Hi Phu - Yes direct connect to 10.4 user geodatabases.  Yes Sql Native Client. Yes we have geometric networks... I can't give you step by step on how to configure the raids, our IT group handled that but I will get back to you with the specs

[nicogis]

Aaron,

which are the queries that arrive at sql server ? (create a trace with sql profile https://msdn.microsoft.com/en-us/library/ms175047.aspx ) and check execution plan if sql server use your spatial index Is my spatial index being used? | Isaac @ MSDN

Remember ArcGIS application doesn't use hint for index ( FAQ: Why do ArcGIS applications not hint the use of SQL Server spatial indexes?  )

[CostonCheatham]

Holy Buckets - what a long thread.  I came across this because we noticed (SQL server 2012) that we have two servers - one dev and the other prod.  On the dev server, we have a feature class (line) with a spatial index (GEOGRAPHY_AUTO_GRID).  It performs well on dev, but the identical setup does not on prod.  What I've found thru tracing the SQL while doing things like panning in ArcMap is that the spatial index on prod is not being used.  If I capture the SQL and use a hint for the spatial index it works just fine.  If I do not hint, the execution plan shows that the spatial index is not used.  I have found that if I drop the spatial index and recreate it with GEOGRAPHY_GRID and medium grid size, it does use the spatial index.

Anyone have ideas there as to why that is the case?  We, at least, have a workaround, but I'd like to resolve the issue just the same.

[nicogis]

As with any other index, when a spatial index is supported, the use of the spatial index is chosen based on cost, so the query optimizer might not choose to use the spatial index even though all requirements for using it are met.

Can you show query and execution plan ?