||SQL Server Tips by Robin Schumacher
Environments That Benefit
From Fragmentation Elimination
One important point regarding fragmentation is not every situation
benefits from fragmentation removal. Before the DBA invests a
considerable amount of time and effort into diagnosing and
attempting the removal of fragmentation, it must first be determined
whether jumping through hoops will actually improve the databaseís
availability and performance.
At the highest level, the environments that benefit the most from
fragmentation removal are read-intensive databases in which large
indexes are being frequently scanned. There are a couple of reasons
why this is the case.
In most OLTP environments, data retrieval tends to be mostly
selective, which negates most of the bad effects of external
fragmentation. OLTP environments also often benefit from internal
fragmentation because it is smart to leave room on index pages that
are being added to and updated frequently. Free index page space
helps avoid the dreaded page split in which a page is divided in two
due to the fact that incoming clustered index data cannot logically
fit on a needed page, so the SQL Server makes room by creating two
index pages out of one. Administrators oftentimes specify a low
fillfactor for their indexes in order to create internal
fragmentation and hopefully avoid page splits.
Data warehousing or databases with many resource-intensive/scanning
queries are another matter. These environments will likely benefit
from fragmentation removal. One reason is that the indexes tend to
be larger, and therefore, are not cached by SQL Server as easily as
small indexes. Fragmentation has no effect on performance once the
data is nestled safely in SQL Serverís memory caches.
Another reason why these environments benefit from fragmentation
removal is the effect fragmentation has on SQL Serverís read ahead
manager. The read ahead manager helps queries that perform large
scans by scanning index pages and data pages that it believes will
be read and placing them into memory before they are actually
needed. Naturally, this process can reduce overall scan times
because data read in memory is many times faster than when that same
data must be retrieved from disk.
However, fragmentation affects how well the read ahead manager
works. The read ahead manager will dynamically adjust the size of
I/O it performs based on the actual physical order of the data and
index pages on the server. When little external fragmentation is
present, the read ahead manager can work very efficiently because it
can read larger blocks at a time; whereas, excessive fragmentation
causes the read ahead manager to read smaller blocks of data. The
end result when fragmentation is present is less overall throughput.
SQL execution patterns are something else to examine. Queries that
perform large index scans are the ones that should see performance
improvements when fragmentation is removed in a database.
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets