Microsoft KB Archive/924947

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

Article ID: 924947

Article Last Modified on 11/20/2007


APPLIES TO

  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Workgroup Edition


SUMMARY

This article describes a scenario in which Microsoft SQL Server significantly increases the unused space for some tables. Then, the article discusses the following two methods that you can use to work around this problem:


  • Convert heap tables to tables that use clustered indexes.

  • Set the open objects configuration option to a high value.


SYMPTOMS

Consider the following scenario in SQL Server:

  • An instance of SQL Server has one or more user databases.
  • The cumulative number of tables in these databases is larger than the specific threshold that is listed in the table at the end of this section. These tables include the system table, the user table, and the temporary table.
  • Applications that are connected to the instance of SQL Server reference most of these tables.

In this scenario, you may notice the following symptoms:

  • The space that these user databases consume grows at a rate that is much faster than the typical rate. Depending on the Autogrowth settings for these user databases, the database files may grow more frequently than they grow in typical cases.
  • The unused part of the space that these user databases consume will be larger than the typical unused part.
  • When you view the properties of the storage structures for these user databases, such as the heap storage structure, the text image tree, and the clustered index, you see lots of unused space.
  • The reserved space for the index entries in the sysindexes table increases by multiples of 8. However, the used space for the index entries in the sysindexes table increases by only a small number, such as 1 or 2. That is, for every eight pages that are allocated in a new extent, only a few pages are ever used from that extent.

The threshold that is discussed earlier in this article varies, depending on the following conditions:

  • The specific edition of SQL Server that you are using
  • The memory that you have configured for SQL Server

The following table lists these thresholds for different editions of SQL Server.

Edition Memory (bytes) Threshold
Enterprise/Standard/Developer More than 671,088,640 8192
Enterprise/Standard/Developer Less than 671,088,640 2048
Enterprise/Standard/Developer Less than 67,108,864 512
Enterprise/Standard/Developer Less than 8,388,608 128
Personal/MSDE Not applicable 128

The memory corresponds to the max server memory setting for the instance of SQL Server that you are using. You can use the sp_configure system stored procedure to configure the max server memory setting.

Note In SQL Server 2005, you can query the sys.dm_db_partition_stats catalog view to obtain the used space information and the reserved information. In SQL Server 2000, you can query the sysindexes table to obtain this information.

CAUSE

SQL Server maintains two types of cached information for space allocation in memory: allocation cache and free space cache. The allocation cache contains extent IDs for recently allocated extents for a specific index. The free space cache contains page numbers for recently allocated pages from these allocated extents. It also contains the state of the pages. If these caches become empty, the problems that are described in the "Symptoms" section occur.

These caches become empty for the following reasons:

  • You execute different commands that must perform synchronizations between the in-memory metadata information and the information on the disk. For example, you execute one of the following commands:
    • DBCC UPDATEUSAGE
    • UPDATE STATISTICS
  • All the object descriptors that are configured by using the open objects option are used. Therefore, no free descriptors are available.


WORKAROUND

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: 

DBCC DBREINDEX
DBCC INDEXDEFRAG

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.

  1. Estimate a buffer size that gives room for temporary tables and worktables that different queries and applications may use.
  2. 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.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

Why the open objects option settings lead to this problem

Whenever a query references a specific object (table), SQL Server loads information about the object into memory. All the index metadata has references to the object information that SQL Server has loaded into memory. The allocation caches that are discussed in this article are associated with a specific index. The open objects option controls the number of descriptors that the instance of SQL Server can load into memory.

When SQL Server starts, SQL Server assigns a specific set of slots, or descriptors, to hold the in-memory version of the object metadata. The number of allocated slots depends on the edition of SQL Server and on the memory that is configured for SQL Server, as described in the “Symptoms” section. All these slots are originally part of a Free List. A specific algorithm is used to assign slots from the Free List for the object metadata. The algorithm has the following two modes of operation.

Mode 1

After SQL Server starts, it uses one of the memory descriptors from the Free List whenever a new object is referenced. SQL Server continues to use all the existing slots when it loads metadata about an object that is referenced in a query. Because there are still slot entries in the Free List, SQL Server continues to use these slots. For existing slots, SQL Server does not reuse memory that was used by another object.

Mode 2

If an object is being newly referenced in a query, and the Free List has become empty, SQL Server scans the existing list of all objects to determine whether there is one slot whose object metadata can be removed from memory. SQL Server must maintain the object metadata in the slot only as long as a query references the table. When that query is finished, SQL Server can reuse the slot if a new query references a different table.

When the object metadata is removed from memory, all its associated index metadata is also removed. When this behavior occurs, SQL Server also loses the allocation cache and the free space cache. Then, when a different query references this table again, all the allocation caches are empty. Therefore, the next time that SQL Server tries to allocate a page, SQL Server tries to allocate a new extent.

If SQL Server continues to reclaim slots that contain object metadata until SQL Server cannot reclaim any slots, SQL Server increases the size of the Free List.

REFERENCES

For more information about how to use the sp_configure stored procedure to change configuration options, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms188787.aspx


For more information about the open objects option, visit the following MSDN Web site:

http://msdn2.microsoft.com/en-us/library/aa196701(SQL.80).aspx


For more information about the max server memory configuration option, visit the following MSDN Web site:

http://msdn2.microsoft.com/en-us/library/ms180797.aspx


For more information about table and index architecture in SQL Server, visit the following MSDN Web site:

http://msdn2.microsoft.com/en-us/library/ms208356.aspx


Keywords: kbtshoot kbprb kbexpertiseadvanced kbsql2005engine KB924947



Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.


Retrieved from ‘https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/924947&oldid=223856’
the Creative Commons 3.0 ShareAlike (except the Microsoft KB Archive).
Powered by MediaWiki