This article elaborates on how fragmentation can affect the performance of the query due to the order of pages and extents in a table. The two types of fragmentation are logical scan fragmentation and average page density. Logical scan fragmentation is the percentage of out-of-order pages in the index in regard to their physical order, rather than their logical order, in the linked list. This fragmentation has a substantial impact on ordered scan operations. This type of fragmentation has no impact on operations that do not rely on an ordered scan, such as seek operations, unordered scans, or lookup operations.
The average page density is the percentage of pages that are full. A low percentage (fewer pages full) has a negative impact on the queries that read the data because these queries end up reading more pages than they could, were the pages better populated. The upside of having free space in pages is that insert operations in these pages do not cause page splits, which are expensive and lead to fragmentation. In short, free space in pages is bad for a data warehouse type of system (more read queries), whereas it is good for an OLTP system that involves many data modification operations. However, you need to remember that this is a balancing act between free space and page splits.
Rebuilding the indexes and specifying the proper fill factor based on your application reduces or removes the fragmentation. Using appropriate data types, that is, char(2) for state, can also be the difference between good performance and page splits when they are updated from empty to populated. You can use the following DMF to find out both types of fragmentation in your index. Be aware that querying this DMF can affect performance because it reads data from both the leaf and nonleaf levels depending on the value (LIMITED, SAMPLED or DETAILED) that is specified for the final parameter of the DMF. For example, to find out the fragmentation for indexes on the New_SalesOrderHeader table, run the following query:
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(‘dbo.New_SalesOrderHeader’), NULL, NULL, NULL)
Look for the avg_fragmentation_in_percent column for logical fragmentation. Ideally, it should be 0, which indicates no logical fragmentation. For average page density, look at the avg_page_space_used_in_percent column. It shows the average percentage of available data storage space used in all pages.
SQL Server 2005 added a feature to build the indexes online — an ONLINE option is added to the CREATE and ALTER INDEX statements. This Enterprise Edition feature enables you to create, drop, and rebuild the index online.Following is an example of rebuilding the index IXNC_SalesOrderID on the New_SalesOrderHeader table:
ALTER INDEX IXNC_SalesOrderID ON dbo.New_SalesOrderHeader REBUILD WITH (ONLINE = ON)
Leave a Reply