POST
|
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
... View more
10-01-2018
05:50 PM
|
1
|
0
|
646
|
POST
|
Hi Brandon, I haven't got this completely documented but I do have something that I can give you: Configuration GISServer (SDE) and MS SQLServer: I haven't got this completely documented but I do have something that I can give you: Configuration GISServer (SDE) and MS SQLServer: 1. Maximum Degree of Parallelism - MAXDOP The default value for MAXDOP is 0 (zero) and can be set or viewed using (sp_configure). A value of 0 means that SQL Server will use all processors if a query runs in parallel. If Hyper-Threading is enabled, MAXDOP should not be 0 and should not be greater than half the number of visible schedulers. A SQL Server with two CPU sockets and a 4 core CPU that does hyperthreading, the maximum degree of parallelism option should be set to 4. Note: The max degree of parallelism configuration option does not limit the number of processors that SQL Server uses. To configure the number of processors that SQL Server uses, use the affinity mask configuration option. Note: Non-uniform Memory Access nodes (NUMA) is mentioned many times. Virtual servers may not have this configured. If your server does have NUMA nodes then you should be aware of the number of cores (with or without hyperthreading) per node. I haven't got this completely documented but I do have something that I can give you: Configuration GISServer (SDE) and MS SQLServer: 1. Threshold for Parallelism Recommend starting with this setting at 50. The default is set to 5 which is a really low setting. Make sure you measure for the critical queries and tuning up or down as appropriate I haven't got this completely documented but I do have something that I can give you: Configuration GISServer (SDE) and MS SQLServer: 1. Spatial Index setting: The default spatial index setting is too low. Grids = (Medium, Medium, Medium, Medium) with cells per object set to 16. It has been recommended to set the cells per object to 64 with Grids = (Medium, Medium, Medium, Low). You can set this per feature class or you can change the default setting settings using sdedbtune: sdedbtune –o alter –k DEFAULTS –P A_MS_SPINDEX –v "GRIDS = (MEDIUM, MEDIUM, MEDIUM, LOW), CELLS_PER_OBJECT = 64" -i {server instance} -D -u -p Note: I have noticed some quirks with the resulting spatial index using the geoprocessing tool. I recommend using the Feature Class wizard (in ArcCatalog select properties for a specific feature class) when adjusting the spatial Index for a specific feature class. I haven't got this completely documented but I do have something that I can give you: Configuration GISServer (SDE) and MS SQLServer: 1. Spatial Statistics: Make sure you rebuild the spatial statistics after rebuilding your indexes. It is also good idea to rebuild the spatial statistics of a feature class after a large data load. Ref: ESRI GeoNet - https://community.esri.com/thread/106931 Jeremiah Peschka – SQL Server Settings – http://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/ Microsoft - MAXDOP Settings- https://support.microsoft.com/en-us/kb/2806535 Stack Exchange – MAXDOP setting - http://dba.stackexchange.com/questions/36522/what-is-a-good-repeatable-way-to-calculate-maxdop-on-sql-server Alastair Aitchison (a good video on how spatial indexes work) - https://sqlbits.com/Sessions/Event5/Creating_High_Performance_Spatial_Databases Cheers, Brian Leung
... View more
07-17-2015
03:03 PM
|
3
|
1
|
1083
|
POST
|
Hi Rebecca, We are running GISServer (SDE) 10.2.1 on SQLServer 2008r2 and SQLServer 2012. Our clients are running ArcMap 10.2.2. I have found that tuning sqlserver and our databases is absolutely necessary as the default settings do yield acceptable performance. Brian
... View more
07-15-2015
09:18 AM
|
0
|
1
|
508
|
POST
|
I found the following helpful as well as the above mentioned spatial index levels. http://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/
... View more
07-14-2015
03:39 PM
|
1
|
2
|
1083
|
POST
|
I think the easiest option is to assign a globalid and then copy it into your guid field. (it has its drawbacks but this is a quick and dirty solution) Brian Leung
... View more
04-30-2010
03:49 PM
|
0
|
0
|
142
|
Title | Kudos | Posted |
---|---|---|
1 | 07-14-2015 03:39 PM | |
1 | 10-01-2018 05:50 PM | |
3 | 07-17-2015 03:03 PM |
Online Status |
Offline
|
Date Last Visited |
01-14-2022
06:57 PM
|