Database Reference
In-Depth Information
With the leaf pages distributed between two extents, ideally you expect to read a range of index rows with a
maximum of one switch between the two extents. However, the disorganization of pages between the extents can
cause more than one extent switch while retrieving a range of index rows. For example, to retrieve a range of index
rows between 25 and 90, you will need three extent switches between the two extents, as follows:
•
First extent switch to retrieve the key value 30 after the key value 25
•
Second extent switch to retrieve the key value 50 after the key value 40
•
Third extent switch to retrieve the key value 90 after the key value 80
This type of fragmentation is called
external fragmentation.
External fragmentation is always undesirable.
Fragmentation can also happen within an index page. If an
INSERT
or
UPDATE
operation creates a page split, then
free space will be left behind in the original leaf page. Free space can also be caused by a
DELETE
operation. The net
effect is to reduce the number of rows included in a leaf page. For example, in Figure
13-3
, the page split caused by the
INSERT
operation has created an empty space within the first leaf page. This is known as
internal fragmentation.
For a highly transactional database, it is desirable to deliberately leave some free space within your leaf pages so
that you can add new rows, or change the size of existing rows, without causing a page split. In Figure
13-3
, the free
space within the first leaf page allows an index key value of 26 to be added to the leaf page without causing a page split.
■
note that this index fragmentation is different from disk fragmentation. the index fragmentation cannot be
fixed simply by running the disk defragmentation tool because the order of pages within a SQL Server file is understood
only by SQL Server, not by the operating system.
Note
Heap pages can become fragmented in the same way. Unfortunately, because of how heaps are stored and how
any nonclustered indexes use the physical data location for retrieving data from the heap, defragmenting heaps is
quite problematic. You can use the
REBUILD
command of
ALTER TABLE
to perform a heap rebuild, but understand that
you will force a rebuild of any nonclustered indexes associated with that table.
SQL Server 2014 exposes the leaf and nonleaf pages and other data through a dynamic management view called
sys.dm_db_index_physical_stats
. It stores both the index size and the fragmentation. I'll cover it in more detail in
the next section. The DMV is much easier to work with than the old
DBCC SH0WC0NTIG
.
Let's now take a look at the mechanics of fragmentation.
Page Split by an UPDATE Statement
To show what happens when a page split is caused by an
UPDATE
statement, I'll use a constructed table. This small test
table will have a clustered index, which orders the rows within one leaf (or data) page as follows:
USE AdventureWorks2012;
GO
IF (SELECT OBJECT_ID('Test1')
) IS NOT NULL
DROP TABLE dbo.Test1;
GO
CREATE TABLE dbo.Test1
(C1 INT,
C2 CHAR(999),
C3 VARCHAR(10)
)
