ADMINISTRATOR
Database indexes are modified when insert, update, or delete operations are made to the underlying data. Over time these modifications can fragment the information in the index. Fragmented indexes can degrade SQL query performance and cause FactoryLogix to respond slowly. To resolve index fragmentation, you can either reorganize or rebuild an index:
- Reorganizing a database index uses a small amount of system resources to defragment indexes on tables and views and compacting the index pages.
- Rebuilding a database index drops, then recreates the index. Rebuilding an index also eliminates fragmentation, reclaims disk space, and reorders index rows.
Reorganizing database tables and indexes can have a big impact on FactoryLogix Database performance. Factors affecting performance include the query type being executed and the available I/O bandwidth on the system.
Using threshold measures such as average page density < 80 percent or logical scan fragmentation > 40 percent to start reorganizing a database are a waste of time and resources for these reasons:
- Some FactoryLogix queuing tables are always highly-fragmented.
- A query reading a single row or a small number of rows that represent the majority of FactoryLogix queries will not benefit from reorganizing a table.
- If there is enough I/O bandwidth and memory for SQL Server on the database server, the impact of table fragmentation might be limited.
To learn more about reorganizing and rebuilding database indexes and defragmentation, consult the Microsoft SQL Server documentation.