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:
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.
Collapse this tableExpand this table
| 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.
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.
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:
- 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. - Estimate a buffer size that gives room for temporary tables
and worktables that different queries and applications may use.
- 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.
Microsoft
has confirmed that this is a problem in the Microsoft products that are listed
in the "Applies to" section.
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.
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:
For more information about the
open objects option, visit the following MSDN Web site:
For more information about the
max server memory configuration option, visit the following MSDN Web site:
For more information about table and index architecture in SQL
Server, visit the following MSDN Web site:
Back to the top
