Create Indexes on SQL Server level, can this corrupt my data?

[DiegoLlamas]

We conducted an investigation at the level of our enterprise SQL Server 2022 database because we have faced some wait time situations in the parcel fabric service, where the system stops responding for several minutes (around 40). It has been found that this wait time corresponds to "Resource_Semaphore". The queries causing these waits enforce the limit of parallelism through the hint "MAXDOP 1". MAXDOP refers to "Maximum Degree of Parallelism", which is a SQL Server configuration parameter that impacts query execution and controls the number of processors used for execution, thus affecting performance. "Incorrect MAXDOP settings can lead to performance bottlenecks, particularly with spatial or versioned queries in ArcGIS."

In our database, the value configured at the instance level is 6 for MAXDOP and at the database level, it is 0 (default = no limit).

Several documented bugs apparently related to this:

• BUG-000151624 - Add MAXDOP 1 to all branch versioned queries that do not have a spatial or attribute-provided query filter.

  • Submitted in 2022 and related to 10.9.1 Utility Network. It was supposedly fixed in patches in 11.1 and 3.1.

• BUG-000176188 - ArcGIS Server feature service REST queries set the Maximum Degrees of Parallelism equal to one (MaxDOP =1) which causes long execution times in the database.

  • Submitted in May 2025 and found in ArcGIS Server 10.9.1. It references the bug below due to documented duplication.

• BUG-000176098 - SQL Server wait event resource_semaphore contention encountered because branch versioned queries on large tables with low selectivity result in the optimizer generating massive memory grants.

  • Submitted in April 2025 and updated in July 2025. Supposedly addressed in ArcGIS Pro 3.5.

We recently updated to 11.5 and the database was updated using 3.5 Pro, so we should be up to date at the Enterprise level. However, our team of editors uses AG Pro 3.3.2 due to the problem, bug of the real curves with WMX...

The question is as follows: our database manager recommends creating 2 indexes, one on the records layer and another on the parcels layer. Both touch attributes like GDB_BRANCH_ID, OBJECTID, and even Shape.

We would like to know if there are no problems in creating indexes on our part on database objects, particularly on these system fields, since with the create indexes tool in Pro, which we use every day, at the database level, in the management studio, it is not reflected in these specific fields. If we create these indexes manually in Management Studio, can we further affect performance or even corrupt the data?

Thanks for you help.

Regards,

Diego Llamas

[Thomas_Brown]

Hello Diego,

BUG-000176098 - SQL Server wait event resource_semaphore contention encountered because branch versioned queries on large tables with low selectivity result in the optimizer generating massive memory grants.

Appears you’re encountering a Microsoft SQL Server issue (thank you for bringing this to our attention), we are working with Microsoft and should have an official issue number soon.

I would be interested in learning more about your environments configuration and how or why you’re encountering the error. Would you mind emailing me directly to discuss further (tbrown@esri.com)?

The issue is not addressed in Pro 3.5, but we will have Enterprise sde_server_config properties one can set which will set the memory allocations on the cursor to help reduce the potential of exhausting the memory and users encountering the resource memory error (note, very odd the system locks up for 40 minutes… we were aware of a few minutes - but the time will based on how long the cursor holding the memory is executing. In the case for the first user to report this to us, they were opening a layers attribute table in Pro for a table with several million rows which would take just under a minute to complete, when multiple users would perform this operation in parallel, they would encounter the error and all sessions would become blocked waiting for the cursor(s) to complete.

This option will be released via Enterprise patches.

Please don’t be distracted by the MAXDOP, it’s not related.

For your index question… it’s safe to add indexes directly in the database vs a tool via an Esri application. I would not recommend adding any indexes which may include the internal branch versioned fields - we can discuss separately.

Thank you.

Tom