Database Maintenance Requirements
The EmpowerID Identity Warehouse requires regular maintenance to maintain optimal performance. SQL Server performance can degrade over time due to the volatile nature of identity and RBAC data, changes in data volume, and query execution patterns. Regular maintenance tasks ensure SQL Server maintains optimal execution plans and database health.
Required maintenance tasks
The following maintenance tasks should be performed on a regular schedule to maintain database performance and integrity:
| Task | Frequency | Command | Purpose |
|---|---|---|---|
| Full Database Backup | Daily | Standard SQL Server backup | Provides complete database recovery point and baseline for transaction log backups |
| Transaction Log Backup | Every 1-4 hours | Standard SQL Server backup | Enables point-in-time recovery and prevents transaction log growth |
| Update Statistics | Daily | EXEC sp_updatestats | Recalculates optimal execution plans for stored procedures based on current data distribution |
| Rebuild Indexes | Weekly | EXEC z_RebuildAllIndexes | Defragments indexes and optimizes data access patterns |
| Consistency Check | Weekly | DBCC CHECKDB | Verifies logical and physical integrity of database objects |
These maintenance tasks should be scheduled using SQL Server Agent jobs or another job scheduling mechanism. Transaction log backup frequency should be adjusted based on your usage patterns and recovery point objectives.
Update statistics
SQL Server continuously calculates optimal execution plans for stored procedures based on estimated data volumes. The sp_updatestats stored procedure recalculates these execution plans and ensures queries compile with up-to-date statistics.
Run this command daily to maintain optimal query performance:
EXEC sp_updatestats
Rebuild indexes
The z_RebuildAllIndexes stored procedure is an EmpowerID-provided utility that rebuilds all indexes in the Identity Warehouse database. Index rebuilding eliminates fragmentation and optimizes data retrieval performance.
Run this command weekly to maintain index health:
EXEC z_RebuildAllIndexes
SQL Server memory configuration
Follow Microsoft best practices for configuring the SQL Server "Maximum server memory" setting to ensure Windows and other applications have sufficient memory to run without paging to disk.
For guidance on SQL Server memory configuration: