ID Number: Q61795
1.00 1.10 1.11 4.20
OS/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.
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 aroundand
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.