Enterprise geodatabase connection slow - machine running SQLServer using 84% memory

[imansbburg]

I've been having painful performance issues connecting to my enterprise geodatabase, and upon finally remote desktop'ing to the machine running SQLServer instance that houses the database, I find that it's using a high percentage of memory.

Normally I consume our data through the map and feature services published as references to this SDE and don't have a problem, but I had some changes to make in the database itself (adding a layer, registering as versioned) hence connecting directly and trying to hit the 'manage' tab. 

What are the best practices for running any kind of reboot or health-checking on the SQL server data source?  We are on SQL Server 2022 and Enterprise Portal 11.3

[DerekLaw]

Hi @imansbburg,

Please take a look at this help topic, Enterprise geodatabase maintenance tasks.

Hope this helps,

[imansbburg]

Thank you. I do have a weekly script for compressing the database and rebuilding indexes-- the task manager showing me high memory use got me wondering what else I can check. 

[DavidPike]

I'd do the normal reports on blocking transactions etc. you would for a normal DB also.  If people are consuming tables directly from the database, look out for stuff like missing spatial indexes on big datasets.

[VenkataKondepati]

A few check points.

• Memory/Resource Monitoring – SQL Server will grab as much memory as it can. Use SQL Server Management Studio (SSMS) to check Max Server Memory settings. By default, it may be consuming everything — leave some headroom for OS/ArcGIS components.

• Regular Index/Statistics Maintenance – rebuild indexes and update statistics on heavily edited feature classes. This keeps query plans efficient and reduces sluggishness in ArcGIS Pro/Manage tabs.

• SQL Server Agent Jobs – schedule nightly index rebuilds and database consistency checks (DBCC CHECKDB) as part of your health routine.

• Reboot Cycle – not usually required often, but if you see memory pressure or long-running connections stuck, a controlled restart during a maintenance window can help clear the cache.

• ArcGIS-Specific Checks – use sdecommand or Pro’s Analyze Dataset tool to check for schema locks, invalid states, or orphaned versions.

• Baseline Monitoring – ArcGIS Monitor or SQL Profiler can help you pinpoint if the bottleneck is at SQL I/O, CPU, or query design.

Regards,
Venkat

[DavidPike]

Thanks ChatGPT

[VenkataKondepati]

Hi @DavidPike,

Thanks for alerting me to not use ChatGPT.
I was using it to format my response and enhance it. I noticed that it is adding some additional unnecessary information.

I will look into it next time.

Regards,

Venkat

[imansbburg]

My IT folks took a look at the machine running SQL server with the database, and did all updates /rebooted, but I still run into the same crippling freeze up when I try to get to the 'manage' menu for a specific feature dataset. It's a dataset I had added a new feature class to, and then I needed to re-register the dataset as versioned. How am I supposed to accomplish this?

[VenkataKondepati]

If you want, I can get on a call with you and see if I can help you.
Please let me know if you are open to join a call, please share the meeting details.