INF: Use of DBCC GAMINIT

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