When would I use dbcc gaminit in SQL Server?

A. This only applies to SQL 6.5 and below.

This can sometimes be useful when you have a very large and fragmented table - typically you will see performance degrade over time. SQL by default tries to fit new rows (inserted via INSERT or BCP) into existing extents that are not full. The information on which extents are full is kept in the gam structure (global allocation map) which is populated dynamically in memory and is cleared every time SQL is started. It is sometimes useful to pre-populate this table fully.

This can be done via dbcc gaminit(<dbid>) which can be put in a startup stored-proc.

Alternatively (or as well as) you could set trace flag 1140. This makes SQL put all new rows that won't fit in the current extent into a new extent rather than searching for old ones. This can lead to wasted space though. See Q174085 for more information.

Re-organising indices with a decent fill-factor when possible will also alleviate the situation.


Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish