topic Re: Poor performance with SDE on SQL Server 2012 in ArcGIS Enterprise Questions

Poor performance with SDE on SQL Server 2012

AaronKreag — Wed, 20 Aug 2014 15:07:49 GMT

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

Re: Poor performance with SDE on SQL Server 2012

JakeSkinner — Wed, 20 Aug 2014 15:55:17 GMT

What version of SDE are you running? i.e. 10.2.2

As a test, can you copy data from the GEOMETRY storage type to SDEBINARY? Is the performance just as slow?

Re: Poor performance with SDE on SQL Server 2012

AaronKreag — Wed, 20 Aug 2014 16:00:51 GMT

Our previous production system was Version 10.0 service pack 5. The new one is 10.2.2. Your note made me think. In our previous version we were running sde schema with direct connect. This new version is all DBO with direct connect. I wouldn't think that would matter. Let me look into that geometry vs sdebinary. Standby.

Re: Poor performance with SDE on SQL Server 2012

AaronKreag — Thu, 21 Aug 2014 14:50:03 GMT

Jake-

I created two new test databases. In one I copied data and used the Geometry config and in the other I used the SDEbinary config. My Geography data type test was a bust because of the datum/coordinate system issue.

A side by side test of just load speeds and in an edit session show they perform the same.

Thank you

Aaron

Re: Poor performance with SDE on SQL Server 2012

DavidColey — Thu, 21 Aug 2014 15:17:02 GMT

Hi Aaron-

We have found that hyperthreading on the CPU must be disabled whether running SQL2008 or SQL2012 when using sql geometry, but not when using binary storage. We lost nearly half of our performance until disabled. We also found immediatly that spatial indices matter greatly with sql geometry, especially for complex polygon datasets like a parcels layer.

The default grid levels(med x 4) and cells per object (16) did not perform well for us at all. After disabling hyperthreading, we set up our dbTune with index levels of (med, med, med, low) with 64 cells per object. We arrived at this level through trial and error as there still doesn't seem to be any effective guidelines- performance has been acceptable since.

I had started a similar thread here, SQL Server 2012 and 2008: Geometry Performance - that asks if there are any performance gains when moving to 2012.

SQL Server 2012 and 2008: Geometry Performance https://community.esri.com/message/96614#96614‌

It's been viewed just a scant 23 times with 0 replies, although this post appears to have done just that-

Thanks

David

Re: Poor performance with SDE on SQL Server 2012

AaronKreag — Fri, 22 Aug 2014 03:05:22 GMT

Ok. So we spent an entire day and night testing. 4 SQL servers. All same OS, Windows 2012 64 bit. same processor, same ram, same data.

1. Sql 2012 Geometry

2. Sql 2012 SDE Binary

3. Sql 2008 R2 Geometry

4. Sql 2008 R2 SDE Binary.

The results were Option 1, 2, and 3 were major fails. Option 4, sql 08R2 with SDE Binary was 2-4 times faster than any of the others, especially when in an edit session. The snapping tool was almost instantaneous in option 4. It was horrible, I mean horrible in 1,2, and 3. We will be converting 1Tb of data back to Windows 2012, 64 bit, SQL 2008 R2 SP2 64bit, and SDE 10.2.2 in the very near future.

Converting to SDE Binary from Geometry in SQL 2012 did nothing. Disabling hyperthreading wasn't even an option.

Please ESRI tell us when this mess is squared away and we can migrate hassle free.

Cheers!

Re: Poor performance with SDE on SQL Server 2012

Anonymous User — Fri, 19 Sep 2014 20:59:26 GMT

We just upgraded our SDE software and spatial Sequel Server databases to 10.2.2 which included converting the geometry configuration for those databases from SDE BINARY to GEOMETRY. We have been having horrible performance issues while in and out of editing sessions including the snapping not working or taking so long that it appears to not be working that you mentioned. We are still using Sequel Server 2008R2. Do you think the change from SDE Binary to Geometry is really the culprit? Did you ever get your system back to an acceptable performance level?

Re: Poor performance with SDE on SQL Server 2012

AaronKreag — Fri, 19 Sep 2014 21:03:42 GMT

Becky,

YES! This is the exact same behavior we had….

We actually created two different SQL servers and ran one in Geometry and one in SDE Binary and the difference is night and day. We use the SDE Binary for editing and then replicate up using a python script to the other sql server running geometry data type for our web map application. The version of SQL server does not matter. 10.2.2. does not like Geometry data type when editing. ESRI needs to get on the ball.

Aaron

Re: Poor performance with SDE on SQL Server 2012

Aaron_Kreag — Mon, 29 Sep 2014 16:37:23 GMT

Becky-

ESRI was asking us some questions from a bug report I filed so we went back to test everything again. It turns out that the SDE - Geometry data that was originally slow is now working fine in an edit session. This data has persisted in the database and has been used by our web map for a little while now. So some theories are that it was related to windows updates, or SQL is somehow becoming more efficient overtime, or we had much more load on the data sets previously when tested.

One thing that we did was, we created brand new data sets in the various configurations and then edited. The editing performance is horrible when the data is newly created. This is even after all of the normal data maintenance and optimization routines have been performed.

So this is leading me to believe that SQL is automatically doing something to the data over time that improves its performance. I am not a SQL engineer so this is a bit out of my area of expertise. Its definitely something worth looking into.

Any luck on your end?

Re: Poor performance with SDE on SQL Server 2012

Anonymous User — Mon, 29 Sep 2014 20:55:58 GMT

our IT and dba folks looked at the hardware, memory, and database performance and saw nothing that was out of the ordinary. But like you, we saw an increased performance over time (about a weeks worth of editing). It seemed to 'fix' itself. or at least our IT and dba said they did not do anything and neither did I and our editing performance is back to normal. Mystery...thanks for the update!

Re: Poor performance with SDE on SQL Server 2012

ZacharyHart — Fri, 03 Oct 2014 12:53:17 GMT

I know this is probably something you've already done, but we use the Native GEOMETRY storage type for our GDB's in SQL 2012 RDBMS and rebuilding the indexes is critical to performance.

Re: Poor performance with SDE on SQL Server 2012

AaronKreag — Fri, 03 Oct 2014 13:11:16 GMT

Zach

Rebuilding spatial index or attribute indexes? According to Microsoft and ESRI Sql2012 natively does the spatial index. Are you overwriting it?

Aaron

Sent via the Samsung Galaxy Note® 3, an AT&T 4G LTE smartphone

Re: Poor performance with SDE on SQL Server 2012

ZacharyHart — Fri, 03 Oct 2014 13:17:28 GMT

Both...I have it scripted to do weekly along with a compress for our replicas. While our database is so very much smaller than yours, we find it critical to performance, especially with editing. Have you contacted the Geodata team? IMO, they are among the best ESRI Technical Support has to offer.

Re: Poor performance with SDE on SQL Server 2012

DavidColey — Mon, 06 Oct 2014 15:33:47 GMT

Correct. When using SQL Geometry there is nothing to rebuild. You can only reset levels and cells per level. We found for dense, complex polygon data sets wiht a lot of varying areas or dense streets datasetw with lots of varying lengths that a index of Medium, Medium, Medium, Low with 64 cells per object greatly outperforms the defeault setting of 4 Medium and 16 cells per object when using both SQL2008 and 2012

Re: Poor performance with SDE on SQL Server 2012

AbhishekSingh — Thu, 09 Oct 2014 07:45:10 GMT

Hi Aaron,

I see that you have mentioned about the bug - NIM082657..

I just wanted to make sure that you are aware about a related patch for this -

ArcGIS 10.1 SP1 for SQL SERVER 2012 Support Patch

ArcGIS 10.1 SP1 for (Desktop, Engine, Server) SQL Server 2012 Support Patch | Samples and Utilities

Abhishek

Re: Poor performance with SDE on SQL Server 2012

AaronKreag — Thu, 09 Oct 2014 07:49:59 GMT

This patch would make sense but we are using 10.2.2. My assumption is that ESRI included that patch in the new release.

That said I did talk with some guys at ESRI the other day about an unrelated issue. The conversation did go to this issue and I was informed that their sql/sde engineers are aware of and have documented more than a couple issues, one of which is listed here and that they are working on trying to find the source of the problem.

Sent via the Samsung Galaxy Note® 3, an AT&T 4G LTE smartphone

Re: Poor performance with SDE on SQL Server 2012

AbhishekSingh — Thu, 09 Oct 2014 08:09:00 GMT

I totally agree with you, the newer versions must have the older issues/bugs addressed prior to its release.

I just thought of mentioning it since the bug was mentioned and the status of the bug still does not show any version on which it is fixed.

Changing the storage type from Geography/Geometry to SDE BINARY is lengthy (for data size you mentioned, using the tool Migrate Storage) but if in case you wish to switch back later back from SDE BINARY to Geography/Geometry the tool does not support that.

Re: Poor performance with SDE on SQL Server 2012

JeffWard — Wed, 22 Oct 2014 17:47:00 GMT

I was experiencing slow edit speeds with our parcel editing. I finally recalculated the extent of our spatial index and that fixed my problem. Link here.

Re: Poor performance with SDE on SQL Server 2012

TONIFAIRBANKS — Mon, 17 Nov 2014 19:40:18 GMT

Did you ever hear back from ESRI about a solution to the slowness problem?

Re: Poor performance with SDE on SQL Server 2012

AaronKreag — Mon, 17 Nov 2014 19:44:20 GMT

According to the tech guy I spoke with a few months ago on the GeoData team he said that the engineers are aware of the problem and that it's getting looked at....

That said there is some chatter about the bypassing the default spatial indexing and building new indexes as something that improves the performance. have not had a chance to test it.

Aaron

Sent via the Samsung Galaxy Note® 3, an AT&T 4G LTE smartphone