High Allocated Unused Space: The Hidden SQL Server Problem You Need to Fix

In my daily work, I use a few monitoring and alerting systems. One of the conditions triggers an alert for highly allocated unused space. This means the table has a significant percentage of allocated but unused space. This likely indicates "internal fragmentation."

What Is High Allocated Unused Space?

High allocated unused space is storage assigned to a SQL Server table that isn’t used. This condition often indicates internal fragmentation. Free space is present within allocated pages. Such fragmentation leads to inefficient storage and can degrade database performance.

Causes of Internal Fragmentation

Several potential causes could lead to this scenario. For example:

• Deleting a significant amount of data from a table - This leaves empty space in allocated pages.

• Updating textual columns - Changing long values to shorter values or NULL reduces page utilization.

• Dropping a column from a table - The space previously used by the column is not reclaimed.

• An improper FILLFACTOR setting - Leaving a high percentage of unused page space during index creation or rebuilding.

Usually, the way to reclaim this space is by INDEX REBUILD / REORGANIZE or DBCC CLEANTABLE, depending on a specific case.

A Real-World Case Study

Recently, I faced a 923GB table with around 600GB of unused space.

After a brief discussion with the customer, it became clear that this is a standard log table. The table is used for inserts and rarely for queries. It is very old, and the customer is unaware of any schema changes. The root cause could be any of the first three typical scenarios.

Just so you know, to identify such cases, we can use for free a Table and Index High Unused Space script from MadeiraData ToolBox on GitHub or system stored procedure

EXEC sp_spaceused 'YourTableName';

I've scheduled maintenance and rebuilt all indexes. This reduced unused space almost to 0! Hooray!

I was shocked to discover a 15GB of unused space the next morning. The day after that - another 10GB appeared, and this increase persisted daily...

Understanding the Cause

After digging deeper, I found that bulk load operations were the criminal!

SQL Server can waste space when we use small batch sizes for bulk loads. This happens because the SQL Server skips checking for available free space. It creates new space, leading to lots of unused space if the batches are too small.

Let's Understand How It Works

• Extent allocation

SQL Server organizes data storage into units called extents. Each extent is 64 KB size and built of eight 8 KB pages.

When we insert data, SQL Server tries to use existing free space within these extents.

• Bulk load operations

During bulk load operations, especially with minimal logging, SQL Server may skip checking for available free space in existing extents.

Instead, it directly allocates new extents to store the incoming data.

Btw, for a bulk load operation with minimal logging tools in SQL Server using BULK INSERT Command and Bulk Copy Program (bcp).

• Why this happens

Skipping the free space check can speed up the bulk load process, which is why minimal logging is used. Minimal logging reduces the amount of data written to the transaction log, making the process faster and using less log space.

If the batch size is small, SQL Server ends up creating many new extents, each with a lot of unused space. This is because the data doesn’t fill up the entire extent, leaving gaps.

Steps to Resolve the Issue

To fix this, here are some simple steps:

• Avoid Small Batch Sizes - Use a batch size that matches the extent size (64 KB) and fits the average row size. This maximizes space use.

  
  

• Use Fully-Logged Inserts for Small Loads - For inserting a small number of rows, use regular INSERT statements. Avoid using bulk loads with minimal logging.

  
  

• Enable Trace Flag 692 for Specific Scenarios - If adjusting the batch size is not an option, consider disabling fast inserts. You can do this by using Trace Flag 692. This prevents bypassing free space checks during bulk loads and reduces unused space. You can enable it with:

DBCC TRACEON(692, -1)

Optimizing Bulk Load Operations

There is a general approach to determining the best chunk size. It applies to bulk load operations in SQL Server. The goal is to balance performance. This includes managing resource usage, such as memory and transaction log space.

Here’s a simplified method to calculate the optimal batch size:

• Estimate Row Size - Calculate the average size of a row in the table by summing the sizes of all columns.

  
  

• Determine Extent Size - SQL Server uses extents, which are 64 KB in size. Each extent consists of eight 8 KB pages.

  
  

• Calculate Batch Size - Divide the extent size by the average row size to determine how many rows fit into an extent.

  Multiply this number by a factor to calculate the batch size. A common factor is 10. Adjust it based on the specific workload and performance testing.

Example Calculation:

• Estimate Row Size: Suppose the average row size is 1 KB.

• Determine Extent Size: Extent size is 64 KB.

• Calculate Batch Size:

  • Number of rows per extent = 64 KB / 1 KB = 64 rows.

  • Optimal batch size = 64 rows * 10 = 640 rows.

The Calculate the optimal batch size for bulk load operations script from MadeiraData ToolBox GitHub will give you a good point for start.

Practical Tips

• Experiment with Batch Sizes

  Start with your calculated batch size, but don’t stop there! Test different sizes like 500, 1000, and 2000 rows. It’s like finding the perfect pair of jeans. Try a few, to see which fits best in your environment.

  
  

• Track Performance

  Use monitoring tools to observe the impact on performance and make adjustments. Think of it as tuning a musical instrument. Keep tweaking until you hit the perfect note!

  
  

• Mind the Transaction Log

  Ensure the batch size doesn’t overwhelm the transaction log. Smaller batches can help manage log growth and reduce locking. Think of it like packing a suitcase. Don’t push everything in at once, or you’ll never get it closed!

  
  

• Avoiding I/O Bursts

  For large batch sizes, consider the impact on the I/O subsystem. Minimal logging writes data pages to disk immediately. This can cause bursts of write I/O. To avoid this, keep batch sizes between 64 KB (one extent) and 4 MB (64 extents). Base this on your system’s disk performance.

Well, now let's back to my case :)

The customer adjusted a chunk size, I've rebuilt an indexes again, and we say goodby for this issue!!

Conclusion

High allocated unused space is more than a storage headache. It’s a performance bottleneck. Understanding its causes and optimizing your bulk load operations can reclaim wasted space. In some cases, this even can boost SQL Server efficiency.

Additional reading and scripts:

• Calculate the optimal batch size for bulk load operations - MadeiraData ToolBox | GitHub

• Table and Index High Unused Space - MadeiraData ToolBox | GitHub

  
  

• Bulk load operations can leave large amounts of unused space - SQL Server | Microsoft Learn

• sp_spaceused (Transact-SQL) - SQL Server | Microsoft Learn

• BULK INSERT (Transact-SQL) - SQL Server | Microsoft Learn

• bcp utility - SQL Server | Microsoft Learn

• DBCC TRACEON - Trace Flags (Transact-SQL) - SQL Server | Microsoft Learn