Paging and Disk Swapping
Good memory management avoids page swapping. To minimize use of operating system files, increase or reallocate physical memory.
Index and Row Fragmentation
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.
Catalog File Growth
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.
Thrashing and Query Execution
Adjust the HASH_THRASHING_PERCENT option to limit thrashing during queries that involve hash algorithms.