Analyzing index fragmentation

The sys.dm_db_index_physical_stats function is used to determine which indexes are fragmented and the extent of their fragmentation.

The function takes the following parameters: database_id, object_id, index_id, partition_number, mode.

database_id - ia an integer value that represents id number of your database
object_number - is an integer value that represents your table or view
index_id - integer that represents id of your index
mode - can be either LIMITED, SAMPLED, or DETAILED

LIMITED: Scans the smallest number of pages, which means this is the fastest mode. The LIMITED mode is equivalent to NULL and DEFAULT.
SAMPLED: Scans 1% of all pages. If an index contains fewer than 10,000 pages, then DETAILED mode is used.
DETAILED: Scans all index pages, which means this is the slowest mode, but most accurate.

You must specify all five parameters, even if their values are null.

Here are some important values returned by sys.dm_db_index_physical_stats function:

index_type_desc - index type (clustered, non-clustere)
avg_fragmentation_in_percent - percentage of the logical index that is fragmented
fragment_count - number of fragments in the leaf level
page_count - number of index or data pages.

An index always has at least one fragment (fragment_count). The maximum number of fragments that an index can have is equal to the number of pages (page_count). For example, an index that is made up of 3 pages can at the most have 3 fragments. The larger the fragment, the less disk I/O that is required. Ideally, the avg_fragmentation_in_percent value should be as close to zero as possible.

Microsoft recommends that you reorganize your index if the avg_fragmentation_in_percent value is less than or equal to 30% and rebuild the index if the value is greater than 30%

I am using the following query to retrieve the fragmentation data:

SELECT OBJECT_NAME(ps.object_id) As TableName, i.name As IndexName,ps.index_type_desc As IndexType, ps.index_depth, ps.index_level, ps.avg_fragmentation_in_percent, ps.fragment_count, ps.page_count 
FROM sys.dm_db_index_physical_stats(DB_ID('Northwind'), null, null, null, 'LIMITED') as ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id 
WHERE NOT i.name IS NULL