Quiet Performance Wins: Scheduling SQL Index Maintenance in Optimizely CMS

This guide walks you through configuring SQL index maintenance in Optimizely CMS (version 12 and newer), from basic scheduling to advanced customization. You'll learn how to set up maintenance windows, adjust fragmentation thresholds, and monitor the results, all without disrupting your users.

Prerequisites

Before you start configuring index maintenance, make sure you have:

• Admin access to your Optimizely CMS instance (you'll need to access the Scheduled Jobs section)
• Basic understanding of SQL Server indexes and how they affect performance
• Access to application settings (for customizing thresholds and timeouts)
• SQL Server 2016 or newer (earlier versions work but have limited online rebuild options)
• Knowledge of your site's traffic patterns (to identify the best maintenance windows)

If you're using Optimizely Commerce alongside CMS, the same scheduled job handles both databases, but you might need to adjust timeout settings for larger catalogs.

Step-by-Step Implementation

Step 1: Locate the Built-in Maintenance Job

Navigate to your Optimizely admin panel and click on Admin > Scheduled Jobs. Look for a job called "Maintain database indexes" or something similar. This built-in job comes pre-installed with Optimizely CMS and handles both reorganizing and rebuilding indexes based on fragmentation levels.

If you don't see this job, check that your Optimizely installation is complete and that database maintenance features weren't disabled during setup.

Step 2: Configure the Schedule

Click on the maintenance job to open its settings. You'll see options for:

• Active: Toggle this on to enable automatic execution
• Schedule: Set when and how often the job runs
• Next Execution: Shows when the job will run next

For the schedule, pick a time when your site has minimal traffic. Most sites see the lowest activity between 2 AM and 4 AM local time. Start with a weekly schedule, Sunday morning at 3 AM is a popular choice.

Step 3: Customize Fragmentation Thresholds

By default, Optimizely rebuilds indexes with over 30% fragmentation and reorganizes those between 10% and 30%. These defaults work well for most sites, but you can adjust them in your appsettings.json:

{
  "EPiServer": {
    "Commerce": {
      "HighFragmentationThreshold": 30,
      "LowFragmentationThreshold": 10,
      "DataBaseIndicesJobCommandTimeOut": 120
    }
  }
}

What these settings mean:

• HighFragmentationThreshold: Indexes above this percentage get rebuilt (complete recreation)
• LowFragmentationThreshold: Indexes between this and the high threshold get reorganized (lighter cleanup)
• DataBaseIndicesJobCommandTimeOut: Maximum seconds the job can run before timing out

For content-heavy sites with frequent updates, consider lowering the high threshold to 25% for more aggressive maintenance.

Step 4: Handle Large Databases

If your database is over 50GB or contains millions of content items, the default 30-second timeout won't be enough. Increase the timeout value gradually, start with 120 seconds and adjust based on job completion logs.

For very large databases, consider splitting maintenance across multiple nights:

{
  "EPiServer": {
    "Cms": {
      "DataBaseIndicesJobCommandTimeOut": 180
    },
    "Commerce": {
      "DataBaseIndicesJobCommandTimeOut": 240
    }
  }
}

Step 5: Set Up Monitoring

After configuring the job, monitor its execution through the job history. Look for:

• Completion time: Jobs taking over 5 minutes might impact morning users
• Error messages: Timeouts or permission issues need immediate attention
• Skipped indexes: Some indexes might be too large for the timeout window

Create a simple SQL query to check fragmentation levels manually:

SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id 
    AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
    AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

Run this query before and after maintenance to verify the job's effectiveness.

Code Examples and Configuration

Custom Maintenance Script for Advanced Control

While the built-in job handles most scenarios, some teams prefer custom scripts for granular control. Here's a basic maintenance procedure you can schedule through SQL Server Agent:

CREATE PROCEDURE [dbo].[CustomIndexMaintenance]
AS
BEGIN
    DECLARE @TableName NVARCHAR(255)
    DECLARE @IndexName NVARCHAR(255)
    DECLARE @Fragmentation FLOAT
    DECLARE @SQL NVARCHAR(MAX)
    
    DECLARE index_cursor CURSOR FOR
    SELECT 
        OBJECT_NAME(ips.object_id),
        i.name,
        ips.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
    INNER JOIN sys.indexes i ON ips.object_id = i.object_id 
        AND ips.index_id = i.index_id
    WHERE ips.avg_fragmentation_in_percent > 10
        AND ips.page_count > 1000
        AND i.name IS NOT NULL
    
    OPEN index_cursor
    FETCH NEXT FROM index_cursor INTO @TableName, @IndexName, @Fragmentation
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @Fragmentation > 30
            SET @SQL = 'ALTER INDEX ['   @IndexName   '] ON [dbo].['   @TableName   '] REBUILD'
        ELSE
            SET @SQL = 'ALTER INDEX ['   @IndexName   '] ON [dbo].['   @TableName   '] REORGANIZE'
        
        EXEC sp_executesql @SQL
        
        FETCH NEXT FROM index_cursor INTO @TableName, @IndexName, @Fragmentation
    END
    
    CLOSE index_cursor
    DEALLOCATE index_cursor
END

Adjusting for Different Database Sizes

Small databases (under 10GB) can use aggressive settings:

{
  "EPiServer": {
    "Commerce": {
      "HighFragmentationThreshold": 20,
      "LowFragmentationThreshold": 5,
      "DataBaseIndicesJobCommandTimeOut": 60
    }
  }
}

Large databases (over 100GB) need conservative settings:

{
  "EPiServer": {
    "Commerce": {
      "HighFragmentationThreshold": 40,
      "LowFragmentationThreshold": 15,
      "DataBaseIndicesJobCommandTimeOut": 600
    }
  }
}

Common Mistakes to Avoid

Running Maintenance During Business Hours

The biggest mistake is scheduling maintenance during active hours. Even "light" reorganization operations can cause blocking on busy tables. Always verify your timezone settings. A job scheduled for 3 AM UTC might run at 10 PM EST.

Over-Maintaining Indexes

Daily rebuilds on databases with minimal changes waste resources and increase SSD wear. We've found that most Optimizely sites perform well with weekly maintenance unless they process thousands of content updates daily.

Ignoring Job Failures

Silent failures are dangerous. If the maintenance job times out regularly, fragmentation accumulates until performance degrades noticeably. Set up email alerts for job failures:

• Go to the scheduled job settings
• Enable "Send email on failure"
• Configure SMTP settings in your application
• Add your team's monitoring email address

Forgetting About Statistics

Index maintenance doesn't always update statistics, which help SQL Server choose efficient query plans. If you notice slow queries despite low fragmentation, update statistics manually:

EXEC sp_updatestats;

Not Testing After Updates

Optimizely updates sometimes reset scheduled job configurations. After platform updates, verify your maintenance schedule and custom settings remain intact.

Testing and Verification Steps

Pre-Maintenance Baseline

Before your first scheduled maintenance, capture baseline metrics:

• Run the fragmentation check query provided earlier
• Note average page load times in Application Insights or your monitoring tool
• Record database file sizes
• Check current CPU and memory usage patterns

Post-Maintenance Validation

After the job runs successfully:

• Check job history: Confirm completion without errors
• Verify fragmentation reduction: Re-run the fragmentation query
• Monitor performance: Look for improved response times over the next 24 hours
• Review logs: Check for any blocking or timeout warnings

Performance Testing

Create a simple load test to measure improvement:

[Fact]
public async Task IndexMaintenanceImprovesFindPagesQuery()
{
    // Arrange
    var stopwatch = new Stopwatch();
    var iterations = 100;
    var timings = new List();
    
    // Act
    for (int i = 0; i < iterations; i  )
    {
        stopwatch.Restart();
        var pages = await _contentLoader
            .GetChildren(ContentReference.StartPage)
            .ToListAsync();
        stopwatch.Stop();
        timings.Add(stopwatch.ElapsedMilliseconds);
    }
    
    // Assert
    var averageTime = timings.Average();
    var baseline = 50; // milliseconds, adjust based on your baseline
    Assert.True(averageTime < baseline, 
        $"Query averaging {averageTime}ms, expected under {baseline}ms");
}

Long-term Monitoring

Set up a monthly review process:

• Export job history to track completion rates
• Graph fragmentation levels over time
• Correlate maintenance windows with performance metrics
• Adjust schedules based on actual fragmentation patterns

Our experience shows that sites often need schedule adjustments after major content migrations or seasonal traffic changes.

Advanced Considerations

Online vs. Offline Rebuilds

SQL Server Standard Edition performs offline rebuilds by default, making tables unavailable during the operation. If you have SQL Server Enterprise Edition, enable online rebuilds in your custom scripts:

ALTER INDEX [IndexName] ON [TableName] 
REBUILD WITH (ONLINE = ON, MAXDOP = 4);

The MAXDOP setting limits parallel processing to prevent CPU saturation.

Handling Commerce Catalogs

Optimizely Commerce databases often need different maintenance patterns than CMS databases. Product catalogs with frequent price updates might benefit from twice-weekly maintenance, while CMS content remains on a weekly schedule.

Integration with Deployment Pipelines

Consider triggering index maintenance after major deployments:

# PowerShell script for post-deployment maintenance
Invoke-Sqlcmd -Query "EXEC dbo.CustomIndexMaintenance" `
              -ServerInstance "your-server" `
              -Database "your-database" `
              -QueryTimeout 600

Conclusion

Setting up proper SQL index maintenance might not be the most exciting task, but it's one of those investments that pays dividends through consistent performance and fewer emergency debugging sessions. Start with the built-in Optimizely job, schedule it for quiet hours, and adjust based on your specific workload patterns.

Remember that perfect index maintenance doesn't exist. You're aiming for good enough performance without excessive resource usage. Monitor your metrics, adjust gradually, and don't forget about those statistics updates.

Working with teams has taught us that the sites with the best performance aren't necessarily those with the most aggressive maintenance schedules, but rather those with consistent, well-monitored maintenance routines that match their actual usage patterns.

Need help analyzing your Optimizely database performance or setting up a maintenance plan that fits your specific content patterns and traffic loads? Our team can review your current index fragmentation levels, identify bottlenecks in your queries, and create a customized maintenance schedule that keeps your site running smoothly without unnecessary overhead. Reach out to discuss how we can help you achieve those quiet performance wins.