Database size depends largely on indexing and data quantity. Create indexes for faster queries. Drop unnecessary objects to free disk space and shorten load times.
Internal index fragmentation occurs when index pages are not being used to their maximum volume.
Row fragmentation occurs when rows are deleted. Deleting an entire page of rows frees the page, but if some rows on a page are unused, the unused space remains on the disk.
DML operations (INSERT, UPDATE, DELETE) on tables can cause index fragmentation.
sp_iqrowdensity reports row fragmentation at the FP index level. See “sp_iqrowdensity procedure,” in “System Procedures,” in Reference: Building Blocks, Tables, and Procedures.
sp_iqindexfragmentation reports internal fragmentation within supplemental indexes. See “sp_iqindexfragmentation procedure,” in Reference: Building Blocks, Tables, and Procedures > System Procedures.
Review the output and decide whether you want to recreate, reorganize, or rebuild the indexes. You can create other indexes to supplement the FP index.
Growth of the catalog files is normal and varies depending on the application and catalog content. The size of the .db file does not affect performance, and free pages within the .db file are reused as needed.
Avoid using IN SYSTEM on CREATE TABLE statements
Issue COMMIT statements after running system stored procedures
Issue COMMIT statements during long-running transactions