Memory and CPU Configuration
SyteLine's IDO layer generates complex queries that benefit from large buffer pool allocations and parallelism settings tuned for manufacturing workloads. The max server memory, MAXDOP, and cost threshold for parallelism settings must be set based on your specific hardware and concurrent user count.
- Max server memory set to total RAM minus 4-8 GB reserved for OS and SyteLine application services
- MAXDOP set to 4-8 based on NUMA node configuration and SyteLine concurrent user count
- Cost threshold for parallelism increased to 50-75 to prevent unnecessary parallel plans for simple IDO queries
- Optimize for ad hoc workloads enabled to reduce plan cache bloat from SyteLine parameterized queries
- Lock pages in memory enabled for SQL Server service account to prevent buffer pool paging
TempDB and Storage Optimization
SyteLine's MRP, APS, and reporting queries make heavy use of tempdb for sorting, spooling, and temporary table operations. A properly configured tempdb with multiple data files on fast storage eliminates a common SyteLine performance bottleneck that manifests as slow form loads and MRP timeouts.
- TempDB data files equal to the number of CPU cores, up to 8, each equally sized
- TempDB on dedicated NVMe or Premium SSD storage separate from user database files
- Trace flag 1118 (pre-SQL 2016) or mixed extents disabled for uniform extent allocation
- Initial tempdb file size set to 1-4 GB each to prevent auto-growth during MRP runs
- Transaction log files on separate physical volumes from data files for write optimization
Index Management for SyteLine Tables
SyteLine's database contains hundreds of tables with factory-defined indexes that do not account for your specific usage patterns. Missing indexes on frequently queried columns and fragmented indexes on high-write tables are the most impactful tuning opportunities. Regular index maintenance is essential for sustained performance.
- Missing index DMV analysis targeting SyteLine tables: item, co, coitem, job, jobmatl, matltran
- Index fragmentation monitoring with rebuild at >30% and reorganize at >10% thresholds
- Columnstore indexes on SyteLine reporting views for large dataset analytical queries
- Filtered indexes on SyteLine status columns (e.g., co.stat where stat = 'O' for open orders)
- Weekly index maintenance job scheduled during SyteLine maintenance windows with ONLINE option
Try it here, free
SyteLine Database Tables Reference
Searchable lookup of the main SyteLine SQL tables, what each holds and its usual business key, for writing queries and reports.
Get a SQL Server health check tailored to your SyteLine database -- our DBAs know manufacturing ERP workloads.