SQL Server 2014含NTEXT列数据库缩短数据留存后备份性能大幅提升原因咨询

Great question! Let’s break down the key reasons behind this surprisingly big backup speed boost—this is a common scenario with large object (LOB) data like NTEXT, and the performance gains often outpace the raw reduction in database size.

• NTEXT’s off-row storage behavior
  NTEXT columns store data outside the main data rows (in off-row pages), which means even a small amount of LOB data can be spread across hundreds or thousands of scattered disk pages. When you had weeks of NTEXT data, those pages were likely fragmented all over your storage volume. Backup processes have to scan every used page, and random I/O (reading scattered LOB pages) is way slower than sequential I/O. By trimming down to 72 hours, you didn’t just remove 6 GB of data—you eliminated a huge number of fragmented, scattered pages that were forcing the backup to do inefficient random reads. The remaining NTEXT data is far more compact and contiguous, so the backup can read data much faster.

• Backup tools skip "empty" pages
  The 105 GB to 99 GB number is the total database size, but SQL Server backups only include pages that contain actual data. Before trimming, your database probably had a lot of LOB pages that were marked as "used" but actually held deleted/expired data (especially if you didn’t run index maintenance after deleting old records). When you removed the old NTEXT data, those pages were marked as free, and the backup process skips them entirely. This means the actual data being backed up might have dropped by way more than 6 GB—maybe 10+ GB in practice—leading to a bigger speedup than the raw size change suggests.

• Reduced log overhead (if using full recovery mode)
  If your database uses the full recovery model, every change to NTEXT data generates significant transaction log entries. With weeks of data, you were probably generating tons of log traffic from new NTEXT inserts and old data deletions. Trimming the retention window cuts down both the volume of new NTEXT data being added and the number of deletions, which reduces log size and the work required for both full backups (which include log data) and transaction log backups. This compound effect can make backups feel way faster than just the data size reduction would imply.

• Fragmentation reduction
  Over time, NTEXT data tends to get heavily fragmented as old records are deleted and new ones are added. Cleaning up weeks of old data gives the database a chance to store new NTEXT data in more contiguous blocks. Less fragmentation means the backup process can read data in larger, sequential chunks, which is much more efficient for disk I/O—this alone can lead to a noticeable jump in backup speed even if the total size reduction seems small.

内容的提问来源于stack exchange，提问作者noble6