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

[CostonCheatham]

Oh, I noticed that what you were suggesting was a hotfix - not a patch.  We don't apply hotfixes unless we know that it fixes a problem - as it suggests, so we probably don't have that loaded.  I can check that out as well.  Thanks so much for the help.

[nicogis]

Thank you for feedback

[Michael_RMiller]

Realizing this post is pretty dated, I'm going to respond anyway in case anyone is searching for this topic.

When we moved to SQL Server 2012 and introduced the Geometry data type for our SHAPE columns, we also felt some performance pain. Our approach was to tweak the spatial index using native SQL Server approaches. This post summarizes our approach. This was implemented in AGS/EGDB 10.2.2.

I am not sure this approach is supported in 10.6. We will be finding out soon.

[ErikLash1]

Also realizing this post is dated, but also that some orgs (us for one) are still on SQL Server 2012 and it's relevant to point out my experience troubleshooting poor performance with the platform over the last several weeks.  We are running enterprise 10.4.1 and SQL Geometry. Found in load testing that the DB was the bottleneck in our system with response times of 5 to 15 seconds while the rest of the system was responding at less than .04 seconds. 

This thread was truly helpful to us in figuring out how to troubleshoot, specifically the list of configurations to change posted above by Brian Leung.

We kept hyperthreading enabled, went to 64k clusters for our data and log files, ran calculations for setting MAXDOP to a more appropriate number (was set to 0 before optimizing), raised the threshold of parallelism to 50 for the machine, and kept default spatial indexing (for tweaking later).

Results were phenomenal. The machine when from returning 2 transactions per second to 22 transactions per second. Response time also balanced out against the rest of the system at less than .04 seconds for response when loaded.

Thanks!

[AaronKreag]

Nice! Thanks for the added info. Pretty crazy this is still an issue so

many years later but I’m sure it will be a continued resource.

Aaron Kreag, GISP

[BrianLeung]

Glad it helped you!

Chasing down spatial performance has felt like tumbling into a very deep dark hole. 

If you want to optimize you spatial indexes even more (and measure the performance) I recommend the following links:

The Black Art Of Spatial Index Tuning In SQL Server | boomphisto  

SQL Server Spatial Indexes - Simple Talk 

Brian Leung