Does Autogrowth Block Updates?

[Sunite]

Does Autogrowth Block Updates?

Q

When a data file autogrows, does autogrowth block updates to the file?



A


No, but autogrowth of data files blocks allocations of new extents because SQL Server blocks changes to the allocation pages, which keep track of which extents in the file are in use, during autogrowth. These allocation pages are the Global Allocation Map (GAM) and Secondary GAM (SGAM). (See SQL Server Books Online-BOL-for details about GAM and SGAM.) Thus, the autogrowth operation blocks new extents so that other operations can't use them. This blocking affects, for example, INSERT and UPDATE operations that require new space allocation. Such INSERT and UPDATE operations must wait until the autogrow operation finishes. Autogrow shouldn't affect INSERT operations that reclaim existing unused space, UPDATE operations that are performed in-place, and DELETE operations that leave free space in a page. Autogrow operations won't affect SELECT operations because retrieving data doesn't involve any new space allocation.