INF: Use of DBCC GAMINIT

 Last reviewed: April 8, 1997
Article ID: Q150748

The information in this article applies to:
  • Microsoft SQL Server, versions 6.0 & 6.5

SUMMARY

SQL Server space allocation is accelerated by an internal structure called the Global Allocation Map (GAM). The command DBCC GAMINIT can proactively populate the GAM, which in a few cases on large databases can give better allocation performance. It is generally not necessary to use this command.

MORE INFORMATION

When SQL Server needs to allocate a new page, this takes place in a sequence designed to keep similar data together. This process involves scanning allocation pages and examining them for free space on the 255 page allocation unit for which they control allocation. Allocation pages occur at regular 256-page intervals throughout the database. A large database may have so many allocation pages that scanning them for free space could take too long. Therefore, SQL Server maintains a per-database memory-resident structure called the Global Allocation Map (GAM), which is a bitmap indicating which allocation pages have already been examined and found to have no free space. By examining the GAM, SQL Server can avoid needlessly scanning allocation pages that have no free space. The GAM is reconstructed on-the-fly as allocation pages are examined and found to be full.

In some cases, if a large database contains long runs of fully allocated space separated by a few free pages, and if the GAM has not been previously initialized for large regions of the database, then transactional throughput can be punctuated by periods when the server is searching for free space. This will not usually be noticed in a typical production environment.

However, it may be beneficial in some cases to proactively populate the GAM using the command DBCC GAMINIT. This will cause SQL Server to scan all allocation pages in the specified database and fully populate the GAM for that database. The command would typically be run once for a particular database after SQL startup to populate the GAM. Once populated, the GAM will stay in effect until the database server is restarted.

Following is the correct syntax: 

   DBCC GAMINIT [(dbid)]
Where dbid is the dbid number of the database. If not specified, the current database is used. Usage example that populates the GAM for dbid 4: 

   DBCC GAMINIT(4)
Usage example which omits the dbid number, using the current database: 

   USE PUBS
   GO
   DBCC GAMINIT

Additional query words: DBCC GAMINIT
Keywords : kbprg kbusage SSrvTran_SQL
Version : 6.0 6.5
Platform : WINDOWS
Issue type : kbhowto

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 8, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.