Why database health matters
ADMINISTRATORON-PREMISE ONLY
Overview
The database is the core engine behind FactoryLogix system performance. Nearly every action within FactoryLogix depends on database queries, including the following:
User logins
Opening processes
Barcode scanning
Switching operations
Generating reports
Saving operational data
When database health declines, performance degrades across the entire system.
Common database health issues
Index fragmentation
Index fragmentation occurs when the logical order of data pages in an index no longer matches their physical order on disk, often due to frequent inserts, updates, and deletes. In Microsoft SQL Server, this can lead to inefficient data access because the system must perform additional reads to locate the necessary pages which increases disk I/O and slows query performance. Fragmentation can also waste storage space and reduce the effectiveness of caching. Regular index maintenance, such as reorganizing or rebuilding indexes, helps restore order and ensures optimal performance.
Fragmented indexes cause the following:
Increased disk reads
Slower query execution
Higher disk I/O load
Outdated statistics
Database statistics are essential because they help the query optimizer understand how data is distributed within tables and indexes, allowing it to choose the most efficient execution plan. In Microsoft SQL Server, database statistics provide insight into factors like data density, row counts, and value distribution, which directly influence whether the engine uses index seeks, scans, or joins. When database statistics are outdated or missing, the optimizer may make poor assumptions, leading to inefficient queries, higher resource usage, and slower performance. Keeping statistics up to date ensures consistent, reliable query performance and helps the system scale effectively as data changes.
When database statistics are outdated, the following may occur:
SQL may choose inefficient query paths
Queries take longer to execute
CPU and disk usage increase
Missing or inefficient indexes
Improper indexing forces Microsoft SQL Server to scan entire tables rather than quickly locating specific records, significantly increasing the amount of data the system must read from disk and process in memory. Instead of performing efficient index seeks, the query engine resorts to full table or index scans, leading to higher CPU usage, longer execution times, and increased I/O load. This becomes especially problematic in large or heavily-used databases, where even simple queries can degrade into expensive operations. Poor indexing strategies such as missing indexes, overly broad indexes, or fragmented ones—can also confuse the query optimizer, resulting in inefficient execution plans. Proper indexing ensures that queries can pinpoint exactly the data they need, reducing resource consumption and improving overall system responsiveness.
Improper indexing significantly slows the following:
WIP Scanning
Analytics reporting
Overall system performance
Unmanaged database growth
Unmanaged database growth can quietly degrade performance and increase operational risk over time. As data accumulates without proper archiving, cleanup, or capacity planning, storage systems become strained leading to slower queries, longer backup and restore times, and higher disk I/O demands. Large, unmaintained databases also make indexing and maintenance tasks less efficient, further compounding performance issues. Without active management, organizations may face unexpected storage costs, extended downtime during maintenance, and difficulty meeting performance expectations as the system scales. Regular database health checks help ensure the system remains optimized for performance.
SQL audit tables (if enabled)
Database audit tables (if used) can impact performance because they continuously capture and store detailed records of system activity—often in high volumes. Every insert, update, or delete operation may generate additional writes to the audit table, increasing disk I/O and transaction overhead. In systems like Microsoft SQL Server, poorly managed audit tables—such as those without proper indexing, archiving, or size control—can grow rapidly and become a bottleneck themselves. This can lead to slower transaction processing, longer query times, and increased maintenance effort if the audit data is not regularly optimized or purged.