We are currently in the process of reorganizing our SDE Structure and want to make the new version as fast and slick as possible. Does anyone know of current documentation available that outlines some general procedures/rules of thumb to follow to increase speed and performance.
Our current set up:
SQL Server 2008
Approximately 40 Database Instances with varying amounts of Feature Datasets and classes
A couple very large feature datasets (50,000+ features)
Some Feature Datasets Versioned
Also any tips on organizing raster datasets in the SDE environment would be greatly appreciated.
Thanks so much!
Here is a good page to start from.
Set up an enterprise geodatabase in SQL Server—Help | ArcGIS for Desktop
There's a number of issues to address, starting with upgrading your database -- SQL Server 2008/2008R2 support is deprecated at 10.3 (not supported by 10.4 & higher [top of p3])
Are your 40 databases really completely independent? Are they all active, or do you need to look at archiving at least some of them to a neutral format (e.g. file geodatabase)?
FYI: "Very large" has a specific meaning in the context of databases, usually referring to tables with more than tens or hundreds of millions of rows. While it's possible for a 50K+ row table to be unwieldy, it's rare that one can achieve "very large".
Even medium to large tables (more than 200k features) which have evolved over time often benefit from spatial defragmentation (export and reimport, or export and rename, as can be achieved using the Sort utility). Actually, the draw performance of all tables improves with spatial sorting, but the difference is often only noticeable over 100-200k+ rows.
In general, performance optimization cannot begin until you accurately capture metrics of existing performance. From there the rule of thumb is to optimize the capabilities with the longest runtime ("longest pole" first).
Rasters in SQL/SDE? Were you planning on using The ST_Raster data type—Help | ArcGIS for Desktop ? If so, note that implementing the ESRI Raster/ST_Pixel data type in any version of SQL results in an unsigned assembly being flagged as "unsafe" by SQL, as ESRI choose to not digitally sign the DLL. Most IT shops will catch this in even a basic security scan and likely turn it off.
I'm going through my own painful and long SDE on SQL 2014 tuning process, with no light at the end of the tunnel. I will say that ultra fast disks, even SSD's, are a critical requirement, not "nice to have", even so, SDE performance out of the box is pretty bad. I'd look at spatial index tuning, but with that many FC's, it's not reasonable to tune the index on each one.