Community
Select to view content in your preferred language

Optimizations of PF MS SQL

125
1
4 weeks ago
Labels (1)
Labels
ThomasHoman
by
Frequent Contributor
4 weeks ago

Hello,

We are currently running an Enterprise 11.3 instance with a MS SQL 2019 for our Parcel Fabric editing and only did a PF conversion from CAD dwgs about 5 months ago so still going through a lot of the cleanup and topological error checking and I had a question about improving the SQL performance as attempting to run a countywide topo validation/error checking results in swamping the SQL server for any other activity - 6 tempdb instances and long list of pending executions in SSMS monitoring. As soon as validation completes performance returns to normal for other non-Fabric users.

Environment:

VMWare on prem.

Enterprise 11.3 (Server, Portal, Datastore, Monitor) deployment as separate MS 2022 servers that don't show strained use in Monitor

MS SQL 2019 - 6 core, 24g, 10tb drive. 

Pro 3.3.5 - did not go to 3.4 due to the angular bug in the traverse tool. Parcel editing workstations on far end of 1gig wan link.

Everything I am seeing suggests that the SQL server is becoming resource bound and slowing down the environment so I am curious what suggestions there might be to improve performance. I think I could ask IT to bump up to 12 cores for testing but need to also verify our license in that regard. Memory doesn't seem to spike much above 5g and drive is only about 20% full so those don't seem impactful.

Thanks in advance for any suggestions.

Tom

0 Kudos
1 Reply
AmirBar-Maor
by Esri Regular Contributor
Esri Regular Contributor
3 weeks ago

@ThomasHoman 

After consulting with the experts:

The best approach for identifying performance problems is to first identify where the time is “going” (and capturing this information before adding more cores). You can start by capturing a SQL Server profile while the user performs the slow operation. If you don’t have the ability to capture, the second option would be using Pro’s ArcGIS Monitor and the log to see which call is consuming the time… this is a little more advanced. You can always contact technical support and open an incident reporting command or workflow xyz is extremely slow and they should be able to provide guidance on how to identify the root cause.