SQL Server significantly increases the unused space for some tables

To work around this problem, use one of the following methods:

• Convert heap tables to tables that use clustered indexes.
• Set the open objects configuration option to a high value.

These methods can reduce the rate of growth for the unused space that is allocated for a table. The following sections describe these two methods. The sections also describe why these methods help resolve the problem that this article describes.

Convert heap tables to tables that use clustered indexes

Note After you convert the heap tables to tables that use clustered indexes, you can perform index maintenance operations periodically to reclaim any space that is unnecessarily unused. For example, you can run the following commands:The following is a typical sequence of operations that occur when you insert a record in a heap table:

• 1.Try to insert a row into the table.
• 2.See the free space cache for index id 0 of this table.
• 3.Determine whether valid pages are present in the free space cache.
• 4.If yes:
  
  • 4.1.If there is sufficient space in the page, insert data in the page.
• 5.If no:
  
  • 5.1.SQL Server must allocate a new page for this row.
  • 5.2.See the allocation cache for index id 0 of this table.
  • 5.3.Determine whether valid extents are present in the allocation cache.
  • 5.4. If yes:
    
    • 5.4.1.Determine whether any of the eight pages in the extent can be used for this new page allocation request.
    • 5.4.2. If yes:
      
      • 5.4.2.1.Allocate one page from this already allocated extent.
      • 5.4.2.2.Go to step 5.5.5.
    • 5.4.3.If no:
      
      • 5.4.3.1.Go to step 5.5.1.
  • 5.5.If no:
    
    • 5.5.1.Use the data from the Global Allocation Map (GAM) and the Secondary Global Allocation Map (SGAM) to find a new extent to be allocated.
    • 5.5.2.Allocate a new extent.
    • 5.5.3.Use one page from this new extent to satisfy the new page allocation request from step 5.1.
    • 5.5.4.Populate the information about this newly allocated extent in the allocation cache.
    • 5.5.5.Populate the information about this newly allocate page in the free space cache.

If the allocation cache and the free space cache are empty between later insert operations, SQL Server will allocate new pages from new extents so that the insert operations can succeed. When the table metadata is removed from memory, the allocation cache and the free space cache are also removed. Therefore, the next time that you perform an insert operation that references the table, these caches are empty. In this situation, SQL Server must perform step 5, and then step 5.5. This behavior causes recently allocated extents to show that eight pages are allocated when only one page is used. In a worst-case scenario, 56 kilobytes (KB) of space may be wasted for every insert operation that you perform on the table.

The following is the typical sequence of operations that occur when you insert data in a table that has a clustered index.

• 1.Try to insert a row in the table.
• 2. Traverse the B-Tree to find the data page in which SQL Server must store the clustering key.
• 3. Determine whether there is sufficient space in the page for the new row.
• 4. If yes:
  
  • 4.1. Insert data in this page.
• 5. If no:
  
  • 5.1. SQL Server must allocate a new page for this row.
  • 5.2. See the allocation cache for index id 1 of this table.
  • 5.3. Determine whether valid extents are present in the allocation cache.
  • 5.4. If yes:
    
    • 5.4.1.Determine whether you can use any of the eight pages in the extent for this new page allocation request.
    • 5.4.2. If yes:
      
      • 5.4.2.1. Allocate one page from this already allocated extent.
    • 5.4.3. If no:
      
      • 5.4.3.1. Go to step 5.5.1.
  • 5.5. If no:
    
    • 5.5.1. Use the data from GAM and SGAM to find a new extent to be allocated.
    • 5.5.2. Allocate a new extent.
    • 5.5.3. Use one page from this extent to satisfy the new page allocation request from step 5.1.
    • 5.5.4. Populate the information about this newly allocated extent in the allocation cache.

If the allocation cache and the free space cache are cleared for reasons that are described in the "Cause" section, there is no immediate need to allocate a new page between later insert operations for this table. This is true as long as the inserted data can fit in the existing page where the specific clustering key must physically reside. When the data page becomes full, and if the caches are empty, SQL Server must perform step 5, and then step 5.5. As this article indicates, if you use a clustered index, the scenario where new pages are allocated occurs much less frequently than the scenario where a record is inserted in a heap table.

The following are two cases where the problem occurs, and the allocation cache and the free space cache are empty. It is assumed that the table's schema allows for 100 rows to fit in a data page.

• If the table has only a heap storage structure, SQL Server could allocate a new extent for every insert operation and use only one page in that extent.
• If the table has a clustered index, SQL Server could allocate a new extent for every 100 insert operations and use only one page in that extent.

Set the open objects configuration option to a high value

This problem occurs primarily because SQL Server uses all the slots in the designated memory area based on the open objects option. When you experience the problem that this article describes, you can set the value of this option to accommodate almost all the tables that will be referenced in the particular instance of SQL Server.

To determine an open objects option value that will prevent this problem, follow these steps:

1. Determine the total number of tables that are present in the specific instance of SQL Server.
   
   Note The tables include user tables and system tables. You must include the tables from the system databases.
2. Estimate a buffer size that gives room for temporary tables and worktables that different queries and applications may use.
3. Add the number of user tables and system tables to the buffer to determine the total number of tables that may be accessed in this instance of SQL Server. This total number is the value that you should set for the open objects configuration option.

Because the memory consumption that is pre-allocated during SQL Server startup is based on this value, you must not set the open objects configuration option to a very high value. If you set the open objects option to a very high value, memory that was originally used for other purposes, such as for query execution and for data buffers, is used instead to maintain the table metadata in the memory area for the open objects option.

We do not recommend that you change the open objects configuration under typical circumstances. Only change this value if you are sure that you are experiencing the problem that this article describes.