[dba-SQLServer] Index Fragmentation

Hi Arthur

Short answer:
A Page Fullness of 62.74% is normally OK.
A Total Fragmentation of 71.67% is bad, and you should rebuild the index
using the statement:
ALTER INDEX myindex ON myschema.mytable REBUILD

Elaborated answer: 
Guess you are getting your counters examining the property of an index using
SQL Server 2005.
This is a convenient way in SQL Server 2005 to get the overall counters for
index fragmentations.
However, you can get more information using this statement, which is a
long-pre-SQL2005:
DBCC SHOWCONTIG(myschema.mytable, myindex)

The DBCC SHOWCONTIG counter: "Avg. Page Density (full)" maps to "Page
Fullness" in the index property of SQL Server 2005.
The DBCC SHOWCONTIG counter: "Logical scan fragmentation" maps to "Total
Fragmentation" in the index property of SQL Server 2005.

A "Page Fullness" or "Page Density (full)" of 62.74% indicates a slack
(unused space) of 37.26 on each page. For an OLTP (read-write) database it's
beneficial to have some amount of unused space, because it prevents forcing
page splits when adding new data. For an OLAP database (read-only database)
the Page Fullness should be near to 100%, because more rows will then fit to
each page, resulting in less IO when reading the data.

A "Total Fragmentation" or "Logical scan fragmentation" of 71.67% indicates
that 71.67% of the pages in your index are not physically adjacent to the
page marked as the "next page" in the header for the index-page.

The Microsoft recommendation is:
If "Total Fragmemntation" <= 30% use:
ALTER INDEX myindex ON myschema.mytable REORGANIZE
If "Total Fragmemntation" > 30% use:
ALTER INDEX myindex ON myschema.mytable REBUILD

REORGANIZE will move the data within the existing pages, resulting in a
higher "Page Fullness" - but it won’t ad new pages.

REBUILD will fill the pages as REORGANIZE doe's, but it will also move
existing pages and allocate new pages to make the pages contiguous according
to the index key.

Asger


-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Arthur
Fuller
Sendt: 12. juni 2008 18:40
Til: Discussion concerning MS SQL Server
Emne: [dba-SQLServer] Index Fragmentation

What is the best practice regarding the Page Fullness and Total
Fragmentation for some particular index. On the one I'm looking at now, I
have Page Fullness 62.74% and Total Fragmentation 71.67%. I'm not sure how
to interpret these numbers.

TIA,
Arthur
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com