Analyzing page fragmentation in SQL Server

Page fragmentation occurs when you make changes to the data in a table (by using the INSERT, UPDATE, and DELETE statements). Oftentimes, these modifications are not usually distributed equally among the rows of the table. For example, there's generally not a row inserted each time a row is deleted. This decreased row density (page fragmentation) can lead to inefficient table scans, causing the query engine to scour over white space in order to find a data row located in the middle of nowhere. You can use the DBCC SHOWCONTIG command to analyze page fragmentation for a database.

You run the DBCC SHOWCONTIG command from the Query Analyzer window. The DBCC SHOWCONTIG statement expects two parameters. The first is the table identification number (ID) for the table you want to analyze. If you don't specify a table ID, then DBCC SHOWCONTIG will check all tables in the current database. The second parameter is the index identification number (ID) for which to check fragmentation information. This parameter is optional.

You can obtain the table ID by querying the sysobjects table of the database. For example, let's say you want to analyze the fragmentation of the publishers table in the pubs database. (Note: The pubs database is part of the default installation of SQL Server.) To find the table ID of the publishers table, use the following query in SQL Server Query Analyzer:

USE pubsSELECT id
FROM sysobjects
WHERE name = 'publishers'

Once you have the table's ID number, you can use DBCC SHOWCONTIG to analyze fragmentation. In SQL Query Analyzer, execute the following query:

USE pubsGODBCC SHOWCONTIG (197575742)

There are three important statistics you can use to identify page fragmentation:

•Avg. Bytes free per page enables you to determine the average number of free bytes on each page scanned. This value is affected by row size. Large row sizes can skew the results into a higher number. A higher number indicates higher page fragmentation. A lower numb

Source: Rozanne Whalen
Viewed 10388 times