Backup Performance Optimization – Data Insights Collected
Hi Team,
As part of our initiative to enhance backup performance for very large databases, I've gathered key data points that will help us fine-tune our strategy:
Least Used Tables (based on last access)
➤ Helps identify cold data that can be backed up less frequently or moved to dedicated subclients — reducing backup volume and time.
LOBs (Large Objects)
➤ Uncompressed and non-secure LOBs are heavy on backup performance.
➤ This data will help us prioritize converting to SecureFiles and enabling compression.
Recently Modified Partitions
➤ Allows us to focus backups only on partitions that have changed — minimizing unnecessary data scan.
Fragmentation Metrics
➤ Identifies bloated tables and indexes contributing to higher I/O during backup.
➤ Supports reorg planning to streamline performance.
Partition Compression Status
➤ Highlights where compression is disabled. Enabling it can reduce read size and speed up backups.
Summary of Key Findings
1. Least Used Tables (by last access timestamp)
Total analyzed tables: 6,780
Identified cold tables (no access in >180 days): 2,150
Recommendation: Move to infrequent/custom backup subclient.
2. Tables with LOBs
Tables with LOB columns: 1,320
LOBs using BasicFile (non-secure): 810
LOBs uncompressed: 760
Recommendation: Target for SecureFile migration and enable compression.
3. Recently Modified Partitions
Partitioned tables checked: 420
Partitions modified in last 30 days: 97
Recommendation: Use for incremental/targeted backup set design.
4. Fragmented Tables (High Wasted Space %)
Tables with >30% fragmentation: 175
Largest fragmentation observed: 3.6 GB wasted in a single table
Recommendation: Include in reorg planning to reduce I/O.
5. Partition Compression Status
Total partitions: 2,340
Partitions with compression disabled: 1,540
Estimated potential space saving if enabled: ~480 GB
Recommendation: Evaluate enabling compression for backup and storage gain.
---
📎 Attachment: backup_analysis_summary.csv
Includes columns for table name, segment size, LOB type, compression, last access date, and partition info.
0 comments:
Post a Comment