Sharing our success story with incident management performance optimization. Our Incident module had accumulated 280K records over 8 years, causing dashboard queries to timeout and report generation to take 5+ minutes.
We implemented an automated archiving solution using SQL Server Agent that moves closed incidents older than 3 years to archive tables. The results were dramatic - incident dashboard load time dropped from 8.2 seconds to 2.2 seconds, and our monthly compliance reports now generate in under 60 seconds.
Key challenge was maintaining data accessibility for audits while improving active query performance. We designed the archive structure to mirror production tables and updated all reports/views to union active and archived data when needed. Happy to share implementation details.
Great approach. What was your archiving criteria? Just closed status and 3 year age, or did you include other factors like severity or investigation outcome? Also curious about your archive table structure - did you maintain all the same indexes or optimize differently?
Here’s the complete implementation summary:
Scheduled SQL Archiving Job:
SQL Server Agent job runs monthly (first Sunday, 2am). Archives incidents closed >3 years with status=‘Closed’ and investigation_complete=‘Y’. Job takes 15-20 minutes for ~3K records.
Archive Table Design:
Created mirror tables: Incident_Archive, IncidentWorkflow_Archive, IncidentAttachment_Archive. Key differences from active tables:
- Clustered index on archive_date instead of incident_id
- Non-clustered indexes only on: incident_number, closed_date, severity
- Removed indexes on assignee, department (rarely queried in archive)
Report and View Updates:
Created unified views for audit compliance:
CREATE VIEW vw_Incident_Complete AS
SELECT * FROM Incident WHERE archive_flag = 0
UNION ALL
SELECT * FROM Incident_Archive
Dashboards query active table only (archive_flag=0). Compliance reports use complete views. Updated 12 reports and 5 dashboards - all backward compatible.
Query Performance Benchmarking:
Measured 5 critical queries before/after:
- Dashboard load: 8.2s → 2.2s (73% reduction)
- Monthly report: 312s → 58s (81% reduction)
- Incident search by number: 1.8s → 0.4s (78% reduction)
- Trend analysis (1 year): 45s → 11s (76% reduction)
- CAPA linkage query: 6.5s → 1.9s (71% reduction)
Used SQL Server Profiler to capture actual execution times over 2-week period.
Edge Case Handling:
Archive job sets archive_flag=1 on active table before moving data. If reopening needed (happened twice in 6 months), we have a stored procedure that moves record back to active tables and clears archive_flag. Takes <5 seconds, maintains full audit trail.
Additional Optimizations:
- Archive tables in separate ARCHIVE filegroup on different disk subsystem
- Partitioned archive by year (2017, 2018, 2019, 2020, 2021)
- Backup strategy: Full backup active tables daily, archive tables weekly
- Retention policy: Archive data kept 10 years, then purged per regulatory requirements
Total database size reduced from 180GB to 95GB after initial archive. Query plan cache more efficient with smaller active dataset. Maintenance windows shortened by 40% since index rebuilds only touch active data.
The combination of proper archive table design, selective indexing, and view-based reporting gave us the performance boost while maintaining complete audit compliance. Would recommend this approach for any module exceeding 100K records.
This is exactly what we need! Our incident queries are painfully slow. How did you handle the audit trail integrity? Auditors need to see the complete history including archived records. Did you modify the standard Trackwise audit views?
For audit compliance, we created unified views that union active and archive tables. The audit trail remains intact - we archive the full incident record plus all related workflow history, attachments, and CAPA links. Auditors use the same reports, they just query the views instead of base tables. No modification to standard audit views needed.
How often does your SQL Agent job run? Daily, weekly? And what happens if someone needs to reopen an archived incident - do you have a restore process? We’re concerned about edge cases where closed incidents might need investigation updates after archiving.
Did you update the dashboard queries to exclude archived data by default, or do users see everything? I’m thinking about performance vs completeness tradeoff. Also, how did you benchmark the 73% improvement - what specific queries did you measure?
Nice work on this. One consideration - make sure your archive tables are in a separate filegroup for easier backup/restore management. Also recommend partitioning the archive table by year if you expect continued growth. This keeps the archive queries performant as the archive grows over time.