Microsoft KB Archive/61795

= INFO: SQL Server Extent Allocation =

Article ID: 61795

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q61795



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 &quot;scattered&quot; 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 &quot;assuming that...&quot; 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 &quot;read-ahead&quot; for the next extent when processing a     query, or update the required multiple extents during processing of the current extent?

A. There is no read-ahead in SQL Server 4.2x. SQL Server 6.0 introduced asynchronous read-ahead. Queries that do table scans or return large result sets in sequential fashion can benefit from this feature.

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

A. The result of the allocation algorithm is that the space does tend to &quot;cluster&quot; 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 &quot;housekeeping&quot; to move the locations of     extents to attempt to make a table &quot;more contiguous&quot;?

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

6. Q. Given the table activity described, is there a &quot;rule of thumb&quot; 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.

Keywords: kbinfo kbother KB61795

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.