INF: SQL Server Extent Allocation Information

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

SUMMARY

An extent is the smallest amount of space (8 contiguous pages) allocated for a table or index. An empty table will have one extent, 16K of space for future use. No further allocation is done until those 8 pages can't hold any more new rows.

MORE INFORMATION

The following questions and responses discuss extent allocation:

1. Q. Is it correct to assume that "scattered" extents will degrade
      responsiveness (presuming that they aren't found in the data
      cache)?

   A. This question requires a complex answer, with many portions
      preceded by "assuming that..." so that the assumption is correct
      given a set of other assumptions made. The performance is
      dependent on overall activity in the database and the entire SQL
      Server. Frequently referenced pages tend to stay in cache,
      contrary to one assumption in this question.

2. Q. Does SQL "read-ahead" for the next extent when processing a
      query, or update the required multiple extents during
      processing of the current extent?

   A. SQL Server reads pages as needed in processing a query. There
      is no "read-ahead."

3. Q. If you BCP out a table, delete its rows, and BCP in the table,
      does SQL Server tend to "cluster" the space allocation as the
      contents are re-entered?

   A. The result of the allocation algorithm is that the space does
      tend to "cluster" both with BCP and in run-time allocation of
      space for tables.

4. Q. If you do a DUMP DATABASE and follow it with a LOAD DATABASE,
      do the table extents get repositioned in a contiguous fashion,
      or does the same physical layout get preserved?

   A. The data copied to a disk or tape with DUMP DATABASE contains
      logical page numbers. The result of a LOAD DATABASE command is
      to have the pages available in a database with the same
      logical page numbers. This is one reason why the destination
      for LOAD DATABASE must be at least as large as the allocated
      space for the database that was the source of the DUMP
      DATABASE.

5. Q. Does SQL Server do any "housekeeping" to move the locations of
      extents to attempt to make a table "more contiguous"?

   A. There is no "housekeeping" done to move allocation around   and
      make tables more contiguous.

6. Q. Given the table activity described, is there a "rule of thumb"
      concerning the frequency of UPDATE STATISTICS? That is, should
      the statistics for the indices in the tables be updated after
      1 percent additional entries, 5 percent, 20 percent, etc.?

   A. The statistics are kept on a special page when an index is
      first built on a nonempty table or as a result of an UPDATE
      STATISTICS command. There is no rule of thumb about the
      frequency of the running of that command.

7. Q. Does SQL Server perform better if a database is 50 percent
      full, rather than 95 percent full, or does it even matter?
      That is, with 50 MB of data, is it better to have a 100 MB
      database than a 51 MB database (ignoring growth)?

   A. The amount of free space in a database affects performance in
      operations that result in allocation of new space for objects.
      So, if growth is ignored, then the amount or percentage of
      free space has no effect.