How to achieve performance isolation in Geodatabase

[gisarchitect]

Hello Everyone,

I'm looking for advice on applying performance isolation mechanisms in SQL Server (this includes CPU, memory, and disk I/O isolation) when hosting a geodatabase. Specifically, how does a geodatabase leverage these features to ensure that the growth of one table does not negatively impact the performance of other tables?

[MarceloMarques · Accepted solution]

@gisarchitect - SQL Server settings for cpu and memory are isolated by SQL Server Instance, if you have more than one instance running on the same host then you need to set the cpu and memory settings for each instance to make sure they do not consume all cpu/memory of the host, also you need to leave at least 16gb memory for the OS, in larger systems might need more, for disk i/o if the host has dedicated disks then you can move each SQL Server Instance databases datafiles to separate disks to improve disk i/o. For more best practices visity my community.esri.com blog page below and read the Production Mapping database guide books for SQL Server for more details.

Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices (esri.com)

[gisarchitect]

Thank you, I do appreciate your answer. If I can ask one more question it would be "Is there any setting or script to control where a new feature class be saved on a certain file group in case I have multiple file groups in the database?" as it seems that this is not doable directly from ArcGIS Pro.

[VinceAngelo]

File group configuration is controlled by the "storage keyword" environment (using an "ON filegroup" in the storage clause). I always create my tables with SQL then register them, so then the file group allocation is trivial. Of course, you can use SQL Server tools to migrate storage via a clustered index as well.

In the days of petabyte disk arrays, file group management is mostly a waste of time for tiny things like vector data under a quarter-billion rows.

- V

[gisarchitect]

Thanks Vince and appreciate your answer. I'm curious to know if you just care with column names, data types, indexes, constraints (regular concerns for any typical table in non-spatial database) during creating your tables? what about geodatabase logic that is created inside the database when creating the table using ArcGIS Pro? Do they created also when registering (Regiseter With Geodatabase GPtool) the table? In other words all what I need is just create the table inside sql server and the tool (Regiseter With Geodatabase GPtool) will care with all geodatabase logic (stored procedures, spatial indexes, etc....)?

[gisarchitect]

I came across this piece of documentation about file groups in geodatabase and I noticed an advice about how to create a feature class on different file group. It is simply to generate a script from any feature class created in the primary file group and change the name of the file group to another one. I have tested it (I successfully created features inside the newly created feature class in a secondary file group) and it seems working, but when trying to run the script of the table inside SSMS, I encountered several errors because of the similar indexes and constraints names. I had to rename them with different names manually. This process would be difficult in case I have a lot of tables in the geodatabase. Is there any automated method (script or tool) to accomplish this? or should I suggest this as an idea in ArcGIS Pro ideas?

https://desktop.arcgis.com/fr/arcmap/latest/extensions/maritime-bathymetry-guide/sql-server/verifyin...

[MarceloMarques]

@gisarchitect  - I am the author of the documentation https://desktop.arcgis.com/fr/arcmap/latest/extensions/maritime-bathymetry-guide/sql-server/verifyin...

You can achieve the goal to store a featureclass in different SQL Server Filegroups by using the ArcSDE Geodatabase DBTUNE. You can learn more about the DBTUNE if you read my SQL Server database guidebook for Production Mapping located in my community.esri.com blog.
Mapping and Charting Solutions (MCS) Enterprise Da... - Esri Community

• Guide Books ArcGIS Pro

You need also read the database connection best practices.

• Database Connections Best Practices

Then, you can download my database template scripts for SQL Server to assist to setup the enterprise geodatabase, those contain the best practices of the guidebooks and a lot more recommendations.

• Database Template Scripts

Finally, to assist to load very large Featureclasses in SQL Server read by blog post below.

How Load Large Featureclass SQL Server Geodatabase

There are also Frequent Asked Questions blog posts that you might be interested.

• FAQ - Frequent Asked Questions

How Load Large Featureclass SQL Server Geodatabase
How to Move the SQL Server Enterprise Geodatabase with a database backup
How to Install Database Clients for ArcGIS
How to Install the SQL Server Client for ArcGIS?

I hope this helps.

[gisarchitect]

Thanks Marcelo. I do appreciate all the documentation you made for geodatabase best practices, they are really helpful and informative. It does worth studying them. I will visit the section of FileGroup and try to apply them. Thanks again.

[MarceloMarques]

@gisarchitect - you are welcome. That's the goal of my community.esri.com blog site, to help database administrators to setup the enterprise esri geodatabase for production with best practices and the best recommendations to achieve and maintain good performance. Enjoy!   : )