--- title: "DB24 | Docs - Index Maintenance" description: "Everything you need to get started with DB24 — from installation guides and configuration to advanced features and best practices." lang: en json-ld: | { "@context": "https://schema.org", "@type": "Organization", "name": "DB24", "url": "https://db24.ai", "logo": "https://db24.ai/logo.png", "description": "Intelligent SQL Server Automation - Automate database management, enhance security, and optimize performance", "contactPoint": { "@type": "ContactPoint", "contactType": "sales", "email": "sales@db24.ai" }, "sameAs": [ "https://www.linkedin.com/company/db24" ] } --- [![DB24 Logo](/lovable-uploads/152150d5-49f3-4c01-b0f4-8365df95171b.png)](/)DB24 Docs Installation Technical Documentation Features Maintenance [Overview ](/docs/maintenance-overview)[Index Maintenance ](/docs/index-maintenance)[Integrity Maintenance ](/docs/integrity-maintenance)[Statistics Maintenance ](/docs/statistics-maintenance)[File Size Maintenance ](/docs/file-size-maintenance) [Overview ](/docs/overview)[Datastore ](/docs/datastore)[Runtimes ](/docs/runtimes)[Portal ](/docs/portal)[Integration ](/docs/integration) Release Notes [Docs](/docs)maintenance # Index Maintenance Copy Link Last updated: May 13, 2026 ## Index Maintenance Index maintenance keeps your SQL Server indexes healthy by detecting and resolving fragmentation before it impacts query performance. DB24 automates this process — evaluating every eligible index on every eligible database, choosing the right action based on fragmentation level and index type, and respecting your configured thresholds and maintenance windows. * * * ## Eligibility Not every database or index is processed on every run. DB24 applies a two-level eligibility check before any maintenance is performed. ### Database eligibility A database must meet all of the following to be included: Condition Rule AG role Must be the primary replica — AG secondaries are never touched Read-only Database must not be read-only Access The service account must have access Exclusions Not listed in the exclusions table (`CategoryId = 100` or `101`) Has eligible indexes At least one index must be present in the maintenance index list ### Index eligibility Each index within an eligible database is then evaluated individually: Condition Rule Index type Rowstore (`type 1–4`) or Columnstore (`type 5–7`) only State Not disabled, not hypothetical Ownership Not a system-owned (MS-shipped) index Allocation unit `IN_ROW_DATA` only for Rowstore Fragmentation At or above the configured minimum threshold Minimum size Must meet both the minimum page count and minimum row count Exclusions Not listed in the exclusions table for this specific database * * * ## Thresholds and Configuration All thresholds are configurable per scope — instance-wide, primary, or secondary — via `cfg.Maintenance`. ### Rowstore indexes Parameter Default Description `MediumFragmentation_RS` 5% Minimum fragmentation to qualify for any maintenance `HighFragmentation_RS` 30% Fragmentation level that triggers Rebuild instead of Reorganize `MaxPDensityForReorg_RS` 80% Page density ceiling for Reorganize — indexes above this are Rebuilt to avoid page splits `MinIXPages_RS` 2,000 pages Minimum page count for inclusion `MinIXRows_RS` 2,000 rows Minimum row count for inclusion ### Columnstore indexes Parameter Default Description `MediumFragmentation_CS` 20% Minimum fragmentation to qualify `MinIXPages_CS` 0 pages No page floor `MinIXRows_CS` 5,000 rows Minimum row count for inclusion ### General settings Parameter Default Description `RunMaintenanceIndex` `1` (instance/primary), `0` (secondary) Master on/off switch per scope `MaxDOP` `2` (instance), `0` (primary/secondary) Parallelism for Rebuild operations `SortInTempDB` `1` (instance), `0` (others) Whether Rebuild sort operations use TempDB `OnlineOption` `0` Rebuilds run offline by default `AllowRowLocks` `1` Row-level locking enabled `AllowPageLocks` `1` Page-level locking enabled * * * ## Maintenance Actions ### Rowstore The action taken depends on both fragmentation level and page density: Fragmentation Page Density Action ≥ Medium and < High ≤ MaxPDensityForReorg (80%) **Reorganize** with `LOB_COMPACTION = ON` ≥ Medium and < High \> MaxPDensityForReorg (80%) **No action** — see note below ≥ High (30%) Any **Rebuild** with configured MAXDOP, ONLINE, SORT\_IN\_TEMPDB, and lock options > **The dead zone:** An index that is moderately fragmented but highly dense (page density above 80%) receives no maintenance. Reorganizing such an index risks excessive page splits and log growth. The index will naturally accumulate more fragmentation over time until it crosses the High threshold and qualifies for a Rebuild. ### Columnstore Columnstore indexes are always **Reorganized** using `LOB_COMPACTION = ON, COMPRESS_ALL_ROW_GROUPS = ON`, regardless of fragmentation level. Rebuild is never used. This is intentional: `COMPRESS_ALL_ROW_GROUPS` flushes all open and closed delta rowgroups into the columnstore, making Rebuild effectively redundant. This behavior requires **SQL Server 2016 or later**. * * * ## Partitioned Indexes Partitioned indexes are evaluated and maintained at the partition level. Each partition is assessed independently, and `PARTITION = N` is appended to the `ALTER INDEX` command where applicable. * * * ## Runtime Behavior A few important behaviors govern how maintenance runs in practice: **Maintenance window is checked per index.** The window is evaluated at the start of every index iteration. If the window closes mid-run, the remaining indexes in that database — and all subsequent databases — are skipped immediately. **Disk space is checked before every index.** Free space is verified across all volumes hosting the database's files before each index is processed. If any volume breaches the configured threshold, the current database is aborted and processing continues with the next. Each volume breach is logged once per run. [Previous DB24 Database Encryption (TDE) ](/docs/transparent-data-encryption)[Next Integrity Maintenance ](/docs/integrity-maintenance)