Database Reference
In-Depth Information
The number of logical reads performed by the individual queries is, respectively, as follows:
Table 'Test1'. Scan count 1, logical reads 8
CPU time = 0 ms, elapsed time = 19 ms.
Table 'Test1'. Scan count 1, logical reads 2542
CPU time = 0 ms, elapsed time = 317 ms.
To evaluate how the fragmented data set affects the number of logical reads, rearrange the index leaf pages
physically by rebuilding the clustered index.
ALTER INDEX i1 ON dbo.Test1 REBUILD;
With the index leaf pages rearranged in the proper order, rerun
--fragmentstats
. The number of logical reads
required by the preceding two
SELECT
statements reduces to 5 and 13, respectively.
Table 'Test1'. Scan count 1, logical reads 6
CPU time = 0 ms, elapsed time = 15 ms.
Table 'Test1'. Scan count 1, logical reads 2536
CPU time = 0 ms, elapsed time = 297 ms.
Performance improved for the smaller data set but didn't change much for the larger data set because just
dropping a couple of pages isn't likely to have that big of an impact. The cost overhead because of fragmentation
usually increases in line with the number of rows retrieved because this involves reading a greater number of
out-of-order pages. For
point queries
(queries retrieving only one row), fragmentation doesn't usually matter, since
the row is retrieved from one leaf page only, but this isn't always the case. Because of the internal structure of the
index, fragmentation may increase the cost of even a point query.
■
the lesson from this section is that, for better query performance, it is important to analyze the amount of
fragmentation in an index and rearrange it if required.
Note
Analyzing the Amount of Fragmentation
You can analyze the fragmentation ratio of an index by using the
sys.dm_db_index_physical_ stats
dynamic
management function. For a table with a clustered index, the fragmentation of the clustered index is congruous
with the fragmentation of the data pages since the leaf pages of the clustered index and data pages are the same.
sys.dm_db_index_physical_stats
also indicates the amount of fragmentation in a heap table (or a table with no
clustered index). Since a heap table doesn't require any row ordering, the logical order of the pages isn't relevant
for the heap table.
The output of
sys.dm_db_index_physical_stats
shows information on the pages and extents of an index
(or a table). A row is returned for each level of the B-tree in the index. A single row for each allocation unit in a heap
is returned. As explained earlier, in SQL Server, eight contiguous 8KB pages are grouped together in an extent that
is 64KB in size. For small tables (much less than 64KB), the pages in an extent can belong to more than one index or
table—these are called
mixed extents.
If there are lots of small tables in the database, mixed extents help SQL Server
conserve disk space.
As a table (or an index) grows and requests more than eight pages, SQL Server creates an extent dedicated to the
table (or index) and assigns the pages from this extent. Such an extent is called a
uniform extent
, and it serves up to
eight page requests for the same table (or index). Uniform extents help SQL Server lay out the pages of a table (or an
index) contiguously. They also reduce the number of page creation requests by an eighth, since a set of eight pages
is created in the form of an extent. Information stored in a uniform extent can still be fragmented, but accessing an
