Database Reference
In-Depth Information
Figure 18-11.
Page fragmentation decreases SQL select performance
When you query the table with a SQL
SELECT
statement, if the data spans multiple pages, the results are
returned more slowly than if the data was all contained within a single page.
Another type of fragmentation that has an absolute measurement is extent fragmentation. SQL Server
data pages are grouped by sets of eight-page structures known as
extents
. Each new table or index created will
be assigned to one page in an extent. As the pages fill, more pages are allocated in either the same extent or a
different extent. This means that tables and indexes can span multiple extents even if they use only two or three
data pages. Tables that use more extents than required represent an additional type of fragmentation, this time at
the extent level.
In either of these examples, SQL select performance will improve if you defragment the table or index.
Database administrators will typically run jobs at night that defragment both.
Microsoft has provided SQL commands that can tell you how fragmented the table is, such as the
DBCC
ShowContig()
command. To give a visual example of a fragmented table, we have run this command and shown
the results in Figure
18-12
.
