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
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.
I just wanted to share my experience on this thread with poor performance on SQL Server 2014 SP1 and ArcGIS 10.3.1.
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
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
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
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
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.
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
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? )
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.
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 ?