From: zdtips@zdtips.com
Sent: Tuesday, March 28, 2000 3:54 PM
To: sqltips@zdtips.com
Subject: SQL Server Tips, 03/29/2000
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 pubs
SELECT 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 pubs
GO
DBCC 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 number of free bytes per page
indicates greater row density and less fragmentation.
* Avg. Page density (full) enables you to identify what
percentage of the page is full. This calculation does take
into account the size of a row, unlike average bytes free
per page. Thus, this number is a more accurate indication
of page density. Optimal page storage occurs at 100 percent.
* Logical Scan Fragmentation enables you to view the percentage
of out-of-order pages in the leaf pages of an index. A page
is out of order when the next page indicated in the Index
Allocation Map (IAM) doesn't match the page pointed to by
the next page pointer in the leaf page. This doesn't apply
to heaps and text indexes. You want this number to be as
close to zero as possible.