SQLServer 2005: DMV's and determiniing fragmentation

Dynamic Management Views (which are not all views, some are stored procedures / functions) are very useful and if you're a DBA working with SQLServer, you should know them inside and out, and have you're list of scripts ready at hand.

Here's a couple that work well for determinining fragmentaion issues:

*Note: Though these can be done on line, the due use CPU and heavy READ I/O - be sure to know your application and how that affects performance, and carfully track performance while they are running the first few times. (Use the DMV options to control how much I/O is done - in the example we use the 'LIMITED' option)

SELECT  substring (b.name,1,40) + '.' +
    substring (c.name,1,50) AS Object,
    a.avg_fragmentation_in_percent AS fragmentation_pct
FROM    sys.dm_db_index_physical_stats
        (
              db_id ('{DBNAME}'),
              object_id ('{TABLE/OBJECT NAME}'),
              NULL,
              NULL,
              'LIMITED'
         ) a
JOIN    sysobjects b
    ON    a.object_id = b.id
LEFT OUTER JOIN sysindexes c
    ON    a.object_id = c.id
AND     a.index_id = c.indid