Table of Contents

SQL Server Maintenance Plan for Enterprise and Large Database Environments

This document describes the recommended SQL Server maintenance plan for large-scale Ometa Framework deployments.

It is intended for environments where one or more of the following conditions apply:

  • Tables contain tens or hundreds of millions of rows
  • Maintenance operations exceed normal maintenance windows
  • The system runs continuous or high-volume workloads
  • Database growth is significant over time
  • Infrastructure resources must be carefully managed

In these environments, standard maintenance strategies may become inefficient or disruptive.

This document provides a more controlled and resource-aware maintenance approach designed to ensure stability and predictable performance at scale.

If your environment does not meet these conditions, refer to: SQL Server Maintenance Plan for Standard Ometa Deployments

Important

If a Database Administrator (DBA) is available in your organization, their guidance should always take precedence. This document provides safe baseline recommendations designed to work in a wide variety of environments, including installations without a dedicated DBA.

Important

These maintenance jobs MUST only target the Ometa Framework database. They are not designed to manage other application or system databases.

Scope and Context

The Ometa Framework database is part of the Ometa product and can be deployed in many different configurations. In some environments, certain tables can grow very large over time.

Examples include:

  • Case
  • CaseProperties
  • Messages
  • Other workflow or integration-related tables

In large customer environments, these tables can contain:

  • Tens to hundreds of millions of rows
  • Continuous growth over time
  • High insert/update activity

This document focuses on maintaining performance and stability for large datasets while minimizing operational risk.

Design Principles

The maintenance strategy in this document follows these core principles:

  • Use proven industry-standard maintenance tooling
  • Use threshold-based maintenance (not schedule-based rebuilds)
  • Separate index maintenance from statistics maintenance
  • Protect application uptime
  • Avoid unnecessary resource consumption
  • Ensure predictable maintenance duration

The Core Maintenance Job (Ola Hallengren)

We rely exclusively on the Ola Hallengren maintenance scripts to automate index and statistics maintenance.

These scripts are widely used in enterprise SQL Server environments and provide safe, configurable, and observable maintenance operations.

The solution separates:

  • Index maintenance (less frequent)
  • Statistics maintenance (more frequent)

This separation is critical for large databases.

Index Reorganize and Rebuilds (Weekly)

Index maintenance handles fragmentation and physical index structure optimization.

Rebuild operations can be resource-intensive on very large tables. Therefore, safety limits are configured to prevent excessive maintenance duration and resource usage.

EXECUTE dbo.IndexOptimize
    @Databases = 'OmetaFramework', /** The name of the Ometa Framework database. **/
    
    -- 1. Fragmentation Rules
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @MinNumberOfPages = 1000,
    
    -- 2. Statistics Rules
    @UpdateStatistics = NULL,

    -- Safety and Resource Controls, can be increased if needed
    @MaxDOP = 6, -- Maximum 6 CPU cores, can be increased or decreased.
    @SortInTempdb = 'Y',
    @TimeLimit = 10800, -- 3 hour maintenance limit
    
    -- 3. Concurrency / Blocking Rules
    @WaitAtLowPriorityMaxDuration = 5,
    @WaitAtLowPriorityAbortAfterWait = 'SELF',
    
    -- 4. Logging
    @LogToTable = 'Y';

Why these specific parameters?

  • @MinNumberOfPages = 1000: Ignores small indexes (< 8MB) to save time.
  • Fragmentation thresholds (5 / 30): Industry standard balance between cost and benefit.
  • @WaitAtLowPriority: Prevents blocking chains during online index rebuilds and protects application availability.
  • @MaxDOP = 6: Limits CPU usage during index rebuild operations to prevent full CPU saturation on multi-core systems. If a server-level MAXDOP is already configured, this parameter can be removed to inherit that setting.
  • @SortInTempdb = 'Y': Moves sort operations to TempDB, reducing pressure on the user database during index rebuilds. This improves rebuild performance in most environments.
  • @TimeLimit = 10800: Stops index maintenance after 3 hours. This prevents maintenance jobs from running indefinitely and protects production uptime. This is especially important for very large indexes.

Important Operational Rule

Fragmentation alone is NOT a sufficient reason to rebuild indexes.

Query performance and system stability should always be the primary indicators for maintenance decisions.

Statistics Maintenance Job (Daily)

Statistics maintenance ensures the SQL Server query optimizer has accurate data distribution information.

This is particularly important for:

  • Large tables
  • Frequently modified tables
  • EAV structures
  • High insert/update workloads

Statistics updates are significantly less expensive than index rebuilds and should run more frequently.

EXECUTE dbo.IndexOptimize
    @Databases = 'OmetaFramework', /** The name of the Ometa Framework database. **/

    -- Indexes disabled
    @FragmentationLow = NULL,
    @FragmentationMedium = NULL,
    @FragmentationHigh = NULL,

    -- Statistics
    @UpdateStatistics = 'ALL',
    @StatisticsModificationLevel = 10,

    -- Logging
    @LogToTable = 'Y';

Why the StatisticsModificationLevel = 10 parameters?

This setting instructs the job to update statistics only when:

  • 10% of rows have changed OR
  • The dynamic SQL Server threshold is reached

This provides a safe balance between:

  • Maintaining optimizer accuracy
  • Avoiding excessive CPU and IO usage

Lower thresholds (for example 5%) can cause unnecessary statistics updates on very large tables.

Database Integrity Check (DBCC CHECKDB)

Database integrity validation is a critical part of database maintenance. Index and statistics maintenance does not detect corruption. Only integrity checks can verify the physical and logical consistency of the database.

The Ola Hallengren maintenance solution includes a dedicated stored procedure for this purpose:

DatabaseIntegrityCheck

This procedure internally executes DBCC CHECKDB in a safe and automated manner.

EXECUTE dbo.DatabaseIntegrityCheck
    @Databases = 'OmetaFramework', /** The name of the Ometa Framework database. **/
    @CheckCommands = 'CHECKDB',
    @LogToTable = 'Y';

Integrity checks can be resource-intensive, especially for large databases. They should run before index maintenance when scheduled on the same day.

CommandLog Table Cleanup

The Ola Hallengren maintenance solution logs all maintenance activity to the dbo.CommandLog table when @LogToTable = 'Y' is enabled.

Over time, this table can grow significantly, especially in environments with frequent maintenance jobs. Regular cleanup is required to prevent unnecessary database growth.

In the following example all CommandLog records older than 60 days will be automatically removed.

EXECUTE dbo.CommandLogCleanup
    @CleanupTime = 1440, -- 60 days
    @LogToTable = 'Y';

Review Maintenance Duration

To verify that the maintenance plan is effective and fits within the maintenance window, DBA's should use the following tracking queries.

A. Review Maintenance Duration & History:

Because we use @LogToTable = 'Y', all actions are recorded. Run this to find long-running rebuilds:

SELECT 
    DatabaseName, SchemaName, ObjectName, IndexName, CommandType, 
    StartTime, EndTime, 
    DATEDIFF(minute, StartTime, EndTime) AS DurationMins,
    ErrorNumber, ErrorMessage
FROM dbo.CommandLog
WHERE CommandType IN ('ALTER_INDEX', 'UPDATE_STATISTICS')
ORDER BY StartTime DESC;

This query helps determine:

  • Whether maintenance fits within the maintenance window
  • Which indexes are expensive to maintain
  • Whether failures occurred

B. Identify Stale Statistics:

Use this query to find statistics that might be suffering from data skew and need manual intervention or a lower modification threshold:

SELECT 
    s.name AS StatsName, 
    sp.modification_counter, 
    sp.rows, 
    sp.last_updated
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE sp.modification_counter > 0
ORDER BY sp.modification_counter DESC;

This query is useful for identifying:

  • High-change tables
  • Data skew scenarios
  • Statistics that may require tuning

Infrastructure Prerequisites

To support massive online index rebuilds on big row tables without crashing the server, verify the following TempDB configurations:

  • Pre-sizing: Ensure TempDB data files are pre-sized to accommodate the largest index rebuild to avoid CPU-heavy autogrowth pauses during the maintenance window. Online index rebuild operations can require up to: 1.2 × index size.
  • File Architecture: Ensure TempDB is split into multiple data files of equal size (4-8 equally sized data files) and placed on the fastest available SSD storage to prevent allocation contention.
  • Transaction Log Capacity: Index rebuild operations generate large transaction log usage, ensure:
    • The transaction log has sufficient free space
    • Autogrowth is configured in large increments
    • Log backups are functioning correctly (if using Full recovery model)
    • Insufficient log space is one of the most common causes of failed index rebuilds.

Deployment & Scheduling

This section explains exactly how to install, configure, and schedule the maintenance solution.

Download

  1. Go to Ola Hallengren's website
  2. Download: MaintenanceSolution.sql

Deploy to SQL Server

  1. Run the script in SSMS:

    -- Run the full script (MaintenanceSolution.sql)

-- Verify installation
SELECT name 
FROM sys.procedures
WHERE name = 'IndexOptimize';

SQL Server Agent Jobs

  1. Create separate jobs through the SQL Server Agent:
Job Purpose Frequency Schedule
Index Maintenance Fragmentation handling Weekly Off-peak e.g. Sunday 01:00
Statistics Maintenance Optimizer accuracy Daily Off-peak e.g. Daily 00:00
Database Integrity Check Corruption check Weekly Off-peak e.g. Saturday 23:00
CommandLog cleaning Clean the logs Daily Off-peak e.g. Daily 05:00

Job Step

Final Rules

DO

  • Use threshold-based index maintenance
  • Update statistics dynamically based on mathematical thresholds
  • Separate index and statistics jobs
  • Monitor maintenance duration and impact
  • Limit maintenance runtime

DO NOT

  • Rebuild all indexes on a (fixed) schedule
  • Run maintenance without logging
  • Ignore maintenance duration
  • Assume maintenance fixes bad queries
  • Run FULLSCAN globally
  • Maintain small indexes (< 1000 pages)