Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  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  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter