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

ISBN: 0-9761573-6-5
Robin Schumacher

 http://www.rampant-books.com/book_2005_2_sql_server_dba.htm