topic Re: Is it absolutely necessary to separate user data from system tables in Data Management Questions

Is it absolutely necessary to separate user data from system tables

yockee — Mon, 29 Aug 2022 04:16:54 GMT

Hi,

I am wondering the reason of separating user data tablespace from SDE system tablespace. I know its better to separate them to avoid SDE tablespace from filling up. But, what if the SDE tablespace has space autoincrement turned on ?

How does the separation of tablespace relate to database backup ? Would it be easier to backup and restore when they are separated or otherwise ?

Need your advice

Re: Is it absolutely necessary to separate user data from system tables

George_Thompson — Mon, 29 Aug 2022 12:48:14 GMT

It is not necessary, but recommended to separate the system tables (SDE schema items) and data owner(s) tables (i.e., DATA / GIS / etc.)

In terms of backup / restore, as long as you export the SDE schema as part of the backup and import the SDE schema FIRST then the other data schemas, then it does not really matter.

I could see how having only one tablespace would make it easier. So ultimately it is your (or the DBA's call).

Re: Is it absolutely necessary to separate user data from system tables

yockee — Mon, 29 Aug 2022 13:15:57 GMT

So, if the reason of separating tablespace is based on avoiding the system table to fill up, then it is not exactly correct because the tablespace, where system table resides, can be set to automatically increment its size. Correct ?

Re: Is it absolutely necessary to separate user data from system tables

George_Thompson — Mon, 29 Aug 2022 14:36:40 GMT

It is not based on avoiding the tablespace to fill up, I think it has to do with a "separation of powers" and limiting potential impact to the separate schemas.

Here is a link to some good Geodatabase admin information; https://community.esri.com/t5/mapping-blog/pro-3-x-database-guide-books-for-mapping-amp/ba-p/1190601

It may not be the exact extensions that you use but is still good information.

Re: Is it absolutely necessary to separate user data from system tables

VinceAngelo — Mon, 29 Aug 2022 14:52:21 GMT

Fragmentation is the thing that kills database performance. By mixing "system" data and "user" data, you increase fragmentation. The auto-increment on the SDE tablespace should be small, while the auto-increment on user data should be large (again, to avoid fragmentation).

The same principle should be applied to OS disks (C: for Microsoft, D:,E:,... for applications; / & usr for Linux, /dataN for applications).

You don't have to conform to best practice, but you don't have to have optimal performance, either.

- V

Re: Is it absolutely necessary to separate user data from system tables

MarceloMarques — Mon, 29 Aug 2022 15:28:11 GMT

yes, the sde geodatabase repository tables must be stored in a separate tablespace from the data owner gis data, this is a database administration best practices and helps with datafiles contention and fragmentation and with maintenance tasks such as gather statistics and rebuild indexes, and to go further you shall separate tables and indexes to get even more benefits in these areas. If you are interested read my database best practices guide books in the link below, it is full of best practices and template scripts that you can use.
Mapping and Charting Solutions (MCS) Enterprise Da... - Esri Community