Is it absolutely necessary to separate user data from system tables

Question

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

MMBR · Answer

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

VinceAngelo · Answer

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

George_Thompson · Answer

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.