- sys.dm_db_index_physical_stats - one row is returned for each *level* of the B-tree in each partition.
- TOTAL FRAGMENTATION - 30% is the magic number from sql 2005 onwards - below and you should do an index re-organise, larger and rebuild.
- Rebuild and reoeganise are options available from the UI.
Prevention
Article on preventing fraggingChoose index keys that are either ever-increasing values or ever-decreasing values. In this type of index setup, newly inserted records will always be placed at either the beginning or end of the logical page-chain, and hence lead to very few (if any) page-splitting as a result of insert operations.
Choose static index keys. If an index key value never changes, it will never have to be moved from the position it is placed in originally as a result of an update to the key values. If an index key is non-static, when the value(s) changes, the record is nearly guaranteed to require being moved to the appropriate new logical position within the index, thereby causing 2 potential side-effects: a) the page that the record was originally on will have a lower density (at least for some time) due to the empty space left by the record moving to a new location - and b) the new page that the record is moved into will likely incur a page-split operation (assuming the new page is already at or near full capacity).
Consider using the -E startup option. This option changes the default database engine behavior from allocating a single extent per allocation request to 4 extents per allocation request. Basically, this would ensure that for each allocation you have 4 contiguous extents for the given object instead of just 1. Interestingly, this option is used in the TPC-H SQL Server TPC tests (possibly others as well, I didn't read them all :-)). This can have a drastic positive impact on the amount of extent fragmentation you see on your system. Of course, in a heavy-allocation workload, this could have negative impact as well