The accumulation of wasted space in database files caused by deleted or modified data that is not reclaimed, leading to unnecessarily large file sizes and degraded performance.
- Database bloat occurs when rows are deleted or updated: The freed space is not automatically returned to the operating system.
- In SQL Server, bloat appears as empty or sparsely populated pages within data and index files: It is detectable via
sys.dm_db_index_physical_stats. - Index bloat is a primary cause of I/O performance degradation: More pages must be read to retrieve the same amount of actual data.
- Bloat is addressed through: Index rebuilding (online or offline), index reorganization, or shrinking database files (use shrink cautiously, as it can cause fragmentation).
- Regular maintenance plans that include index rebuild/reorganize operations help: Prevent bloat from accumulating over time.
- Relevant Idera tools: SQL Defrag Manager automates index maintenance to eliminate bloat and maintain optimal index density.
- Related terms: Fragmentation, Index Rebuild, Page Split, DBCC SHRINKFILE.
