This article explains four concepts that help you assess SQL Server indexes: pages, fill factor, fragmentation, and size. Understanding these concepts helps you decide whether maintenance is worthwhile. They should be considered along side query performance and the cost of maintenance.
Pages and index structure
SQL Server stores data in fixed-size pages of 8 KB. Pages contain records and supporting information, such as page headers and row offsets. Not every page contains index keys or pointers; its contents depend on its purpose.
Traditional SQL Server rowstore indexes use a balanced B+ tree:
- The root page is the starting point for a search.
- Intermediate pages guide the search toward the appropriate leaf page.
- Leaf pages contain the table rows in a clustered index, or index entries and row locators in a nonclustered index.
Index depth is not fixed at two or three levels. It depends on the number of entries, their size, and how many entries fit on each page. Wider keys and larger indexes can require more levels. SQL Server index architecture
PostgreSQL also normally uses 8 KB pages, but its storage structures and maintenance behavior differ. The SQL examples below apply specifically to SQL Server. PostgreSQL page layout
Fill factor and page density
Fill factor controls how full SQL Server makes leaf pages when an index is created or rebuilt. A fill factor of 80 leaves approximately 20% of the available leaf-page space free for future growth.
Values of 0 and 100 are equivalent: SQL Server fills leaf pages to capacity, subject to record size and page overhead. It does not continuously preserve the chosen percentage as data changes.
A lower fill factor can reduce disruptive page splits when inserts or expanding rows need space within existing pages. However, it also requires more pages, increasing storage, memory, and potentially read I/O.
Do not automatically start every index at 90%. Keep the default unless measurements show that a lower value benefits a particular index. Microsoft’s fill-factor guidance
Page density is the actual percentage of page space currently occupied. Fill factor is a setting; page density is a measurement. They are not interchangeable.
Changing an index’s fill factor
For ALTER INDEX, the required permission is ALTER on the table or view. Membership in a powerful role such as sysadmin or db_owner can provide sufficient permissions, but is not additionally required when the necessary permission has been granted.
The following example rebuilds an existing index with a fill factor of 80. This illustrates the syntax; it does not establish that 80 is appropriate for your workload.
USE [DesignWarehouse];
GO
ALTER INDEX [NonClusteredIndex-20260924-110000] ON [dbo].[Image_Library] REBUILD WITH (FILLFACTOR = 80);
GO
The index must already exist under the specified name. ALTER INDEX syntax and permissions
Fragmentation and index size
Logical fragmentation occurs when index pages are physically arranged differently from their logical key order. It can affect queries that scan many pages, but its impact depends on storage and workload.
Size provides context. A small index with high fragmentation may not warrant maintenance, while a large index with poor page density may cause substantial extra reads. Neither measurement alone proves that an index is performing poorly. Microsoft’s maintenance guidance
The following read-only query reports rowstore indexes in the current database, with one result per partition. Its size calculation covers leaf-level, in-row pages, not the index’s complete storage footprint. It excludes upper tree levels, separately stored large objects, and row-overflow storage.
SELECT DB_NAME() AS database_name,
OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
OBJECT_NAME(p.object_id) AS table_name,
i.name AS index_name,
p.partition_number,
p.page_count AS leaf_page_count,
CAST(p.page_count / 128.0 AS decimal(18,2)) AS leaf_in_row_size_mb,
CAST(p.avg_fragmentation_in_percent AS decimal(6,2)) AS fragmentation_pct,
i.fill_factor,
CASE
WHEN p.page_count < 1000 THEN 'Small: usually low priority'
WHEN p.avg_fragmentation_in_percent < 5 THEN 'Low fragmentation'
WHEN p.avg_fragmentation_in_percent < 30 THEN 'Moderate fragmentation: assess impact'
ELSE 'High fragmentation: assess impact'
END AS assessment
FROM sys.dm_db_index_physical_stats
(DB_ID(), NULL, NULL, NULL, 'LIMITED')
AS p JOIN sys.indexes AS i
ON i.object_id = p.object_id
AND i.index_id = p.index_id
JOIN sys.tables AS t
ON t.object_id = p.object_id
WHERE i.type IN (1, 2)
AND i.is_disabled = 0
AND i.is_hypothetical = 0
AND t.is_ms_shipped = 0
AND p.index_level = 0
AND p.alloc_unit_type_desc = 'IN_ROW_DATA'
ORDER BY p.page_count DESC;"
LIMITED reduces scanning work but does not report page density. Investigate selected indexes with SAMPLED or DETAILED mode when needed. On SQL Server 2022, reporting across the database requires VIEW DATABASE PERFORMANCE STATE or sufficient higher permissions. Even read-only physical-statistics scans consume resources. Physical-statistics documentation
Choosing whether to reorganize or rebuild
The familiar 5% and 30% fragmentation thresholds are screening guidelines, not mandatory action points. Likewise, 1,000 pages is a practical prioritization threshold, not a guarantee that smaller indexes never need attention.
Reorganize
reorders and compacts leaf pages. It operates online and does not update statistics.
Rebuild
recreates the index. It can apply a new fill factor and updates the index’s statistics, with sampling behavior depending on the operation.
Update statistics
when inaccurate estimates are the underlying problem; a rebuild may be unnecessary.
Microsoft recommends measuring the benefit rather than maintaining indexes solely to reduce fragmentation percentages. Index maintenance strategy
For example, an index with 10,000 pages and 75% fragmentation deserves investigation, but does not automatically require rebuilding. An index with 1,000 pages and 25% fragmentation might benefit from reorganizing—or need no maintenance at all.
An offline rebuild does not require taking the entire database offline, but it can block access to the affected table. Supported online rebuilds allow concurrent access during most of the operation, although they still require locks at certain stages. They also introduce overhead; their duration depends on the workload and available resources. Rebuild options
Choosing whether to reorganize or rebuild
The familiar 5% and 30% fragmentation thresholds are screening guidelines, not mandatory action points. Likewise, 1,000 pages is a practical prioritization threshold, not a guarantee that smaller indexes never need attention.
Reorganize
reorders and compacts leaf pages. It operates online and does not update statistics.
Rebuild
recreates the index. It can apply a new fill factor and updates the index’s statistics, with sampling behavior depending on the operation.
Update statistics
when inaccurate estimates are the underlying problem; a rebuild may be unnecessary.
Microsoft recommends measuring the benefit rather than maintaining indexes solely to reduce fragmentation percentages. Index maintenance strategy
For example, an index with 10,000 pages and 75% fragmentation deserves investigation, but does not automatically require rebuilding. An index with 1,000 pages and 25% fragmentation might benefit from reorganizing—or need no maintenance at all.
An offline rebuild does not require taking the entire database offline, but it can block access to the affected table. Supported online rebuilds allow concurrent access during most of the operation, although they still require locks at certain stages. They also introduce overhead; their duration depends on the workload and available resources. Rebuild options
Monitoring and automation
Schedule monitoring separately from maintenance. For this workload, a weekly report is a reasonable starting point; increase the frequency if data changes or performance trends justify it.
Use SQL Server Agent to collect results from each eligible database and retain history. An initial review report might flag indexes with at least 1,000 pages and more than 5% fragmentation, but those filters should not automatically trigger rebuilds.
SQL Server Management Studio’s Maintenance Plans can also schedule reorganize, rebuild, and statistics tasks through SQL Server Agent. Configure their scope carefully rather than running every operation against every index. Maintenance Plan Wizard
Finally, treat index removal as a separate decision. Small size or a short period without recorded reads does not make an index unnecessary. Check a representative workload—including infrequent reporting—and determine whether the index supports a primary key or uniqueness requirement before considering removal. LIMITED reduces scanning work but does not report page density. Investigate selected indexes with SAMPLED or DETAILED mode when needed. On SQL Server 2022, reporting across the database requires VIEW DATABASE PERFORMANCE STATE or sufficient higher permissions. Even read-only physical-statistics scans consume resources. Physical-statistics documentation.
High fragmentation, low page density, and a large page count make an index a candidate for maintenance, but no universal numeric threshold makes rebuilding mandatory. Rebuild when measurements show that reorganizing is insufficient or less practical, or when a required structural or configuration change calls for rebuilding. Use query performance before and after maintenance to validate the decision. Microsoft explicitly advises against choosing maintenance solely from fixed fragmentation or page-density thresholds. Microsoft’s index maintenance guidance
MySQL
For typical InnoDB tables, statistics can recalculate automatically. If query plans become poor after substantial data changes, you can explicitly refresh them:
This updates optimizer statistics without rebuilding the table. MySQL statistics documentation
When substantial deletes or changes leave wasted space, the heavier option is:
For InnoDB, this rebuilds the table and its indexes. It is closer to a table rebuild than SQL Server’s lightweight reorganize. It requires resources and can involve locking; space returned to the operating system depends on tablespace configuration. Don’t schedule it for every table simply because a week has passed. MySQL OPTIMIZE TABLE
MySQL’s built-in Event Scheduler can run scheduled SQL, including maintenance commands. Event Scheduler
PostgreSQL’s autovacuum cleans up space for reuse, but it doesn’t rebuild indexes. Also, maintenance commands can affect performance while running.
Here’s a beginner-friendly version:
MySQL and PostgreSQL both do some housekeeping automatically. You usually don’t need to rebuild every index on a schedule.
In MySQL, using its usual storage engine, InnoDB:
ANALYZE TABLErefreshes information that helps MySQL choose an efficient way to run queries.
OPTIMIZE TABLErebuilds a table and its indexes to reorganize storage and potentially reclaim unused space. It’s a bigger job, best used when there’s a demonstrated need.
Event Scheduler
can run maintenance commands automatically at chosen times.
In PostgreSQL:
Autovacuum
runs in the background. It removes old row versions that are no longer needed and makes their space available for reuse. It also arranges statistics updates.
REINDEXrebuilds an index when necessary—for example, when it has accumulated excessive unused space.
REINDEX CONCURRENTLYlets normal reads and writes continue during most of the rebuild, although it takes more work and can still wait for other activity.
VACUUM FULLrewrites and compacts a table, but blocks access while it runs. It isn’t routine maintenance.
For either database, start by checking slow queries and unexpected storage growth. Perform heavier maintenance when it solves a measured problem.
These distinctions follow the official MySQL maintenance documentation, PostgreSQL vacuuming guidance, and PostgreSQL REINDEX documentation.