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]

Good move I would say,

[nishadwijesekara]

Spatial views should be managed by using ArcSDE commands (use v 10.2.2) if you use SDE binary Storage type on 10.3

[ScottFierro2]

While this may not be the magic bullet for everyone it sure made a huge difference for us. Everyone is turning to the DB side which is logical but an OS change was night and day for us. We had MS in for an unrelated issue doing a Enterprise Health Check and they brought this to our attention.

We run 2008R2 SP1 for the OS on our box with SQL 2012 and perform all of our GIS activities against this.

By default the OS will be set to use 4k bytes per sector storage. Change this to use 64k bytes per sector. The downside is you will have to offload all your DB's to re-configure the OS and then re-deploy them but it was well worth it for us.

We currently have this single box and a single DB in SQL and it's handling over 1,000 connections, all the day-to-day user usage and editing workflows, a multitude of cross DB platform processes and ETL's with Oracle, over 150 ArcServer services (GP's, map, wfs primarily) and those support a variety of heavily used apps including our public facing web-mapping site and it hums along. We are working through implementing a second DB on the same box now to facilitate a massive enterprise deployment of Collector using AD FS integration and thus far still seen no performance issues.

Obviously, as many have discussed establishing your geom settings, dbtune configs and routine maintenance will be key as well. Hope this helps at least some of you.

[Yann-EricBoyeau]

Hi Scott,

Could you confirm using 64k bytes per sector was the key for you to have great performance with your Enterprise Geodatabase on SQL server 2012 with GEOMETRY storage ?

Or are you using SDEBINARY storage ?

Currently I had to go back to SDEBINARY in SQL2012 to get decent performance.

[ScottFierro2]

That's correct. We run SQL 2012 and leverage the MS GEOMETRY type not the ESRI AcrSDE storage type SDEBINARY. We had seen hit or miss performance with the systems initially and had thought it was simply tied to some of the large datasets we were using. Once we learned of the configuration option for the sectors we spun up a test VM and ran through some workflows and saw the massive performance gains. With that we implemented a migration plan and moved off the old 4k sector configs to new VM's with the 64k config and have been steaming along ever since.

[Yann-EricBoyeau]

Hi Scott,

Just changed the partition sector config to 64k. And yes the performance with ST_GEOMETRY and SQL Server 2012 is now good !

But just "good", not "great". I can still notice a 50% performance increase if I use SDEBINARY instead of ST_GEOMETRY.

Anyway both formats are now usable depending the need.

[MelissaJarman]

I believe you are referring to SQL Server GEOMETRY type and not ST_GEOMETRY.

[AaronKreag]

Yes.

[ScottFierro2]

That's great glad it helped you out and I agree that it all becomes subject to your underlying data complexities and usage needs. Why I had said it's not the silver bullet but should be enough to make things usable for anyone. It then puts you into position to focus on individual use case scenarios or spreading data through several DB's configured differently for usage needs or data complexities for optimization.

[ThomasColson]

I've found, with SDE 10.3.1 on SQL 2014, for both GEOMETRY and GEOGRAPHY storage types, setting the spatial index to AUTOGRID (which give you 8 levels) and 1028 cells is a good middle-performance gain. If you're using 2014 enterprise, you could get really crazy and move some of your heavy hit spatial tables into "In Memory Tables", an experiment I'm soon to try.