--查询数据库db中表tb的所有索引的随片情况 
 use db 
 go 
 select  
 a.index_id,---索引编号 
 b.name,---索引名称 
 avg_fragmentation_in_percent---索引的逻辑碎片 
 from 
 sys.dm_db_indx_physical_stats(db_id(),object_id(N'create.consume'),null,null,null) as a 
 join 
 sys.indexes as b 
 on 
 a.object_id=b.object_id  
 and 
 a.index_id=b.index_id 
 go 
  
 ---解释下sys.dm_db_indx_physical_stats的参数 
 datebase_id: 数据库编号，可以使用db_id()函数获取指定数据库名对应的编号。 
 object_id: 该索引所属表或试图的编号 
 index_id: 该索引的编号 
 partition_number:对象中分区的编号 
 mode:模式名称,用于指定获取统计信息的扫描级别。 
  
  
 有关sys.dm_db_indx_physical_stats的结果集中的字段名去查下联机丛书。 
  
  
 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2010/01/12/5183068.aspx

--检查需要重建索引的表.
SELECT OBJECT_NAME(dt.object_id),
        si.name,
        dt.avg_fragmentation_in_percent,
        dt.avg_page_space_used_in_percent
FROM
    (SELECT object_id,
        index_id,
        avg_fragmentation_in_percent,
        avg_page_space_used_in_percent
        FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
        WHERE   index_id <> 0
    ) AS dt --does not return information about heaps
        INNER JOIN sys.indexes si
            ON    si.object_id = dt.object_id
            AND si.index_id  = dt.index_id

---------------------------------------------------
--维护索引
SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
       CASE
              WHEN ps.avg_fragmentation_in_percent > 15
              THEN 'REBUILD'
              ELSE 'REORGANIZE'
       END +
       CASE
              WHEN pc.partition_count > 1
              THEN ' PARTITION = ' + CAST(ps.partition_number AS nvarchar(MAX))
              ELSE ''
       END,
       avg_fragmentation_in_percent
FROM   sys.indexes AS ix
       INNER JOIN sys.tables t
       ON     t.object_id = ix.object_id
       INNER JOIN sys.schemas s
       ON     t.schema_id = s.schema_id
       INNER JOIN
              (SELECT object_id                   ,
                      index_id                    ,
                      avg_fragmentation_in_percent,
                      partition_number
              FROM    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)
              ) ps
       ON     t.object_id = ps.object_id
          AND ix.index_id = ps.index_id
       INNER JOIN
              (SELECT  object_id,
                       index_id ,
                       COUNT(DISTINCT partition_number) AS partition_count
              FROM     sys.partitions
              GROUP BY object_id,
                       index_id
              ) pc
       ON     t.object_id              = pc.object_id
          AND ix.index_id              = pc.index_id
WHERE  ps.avg_fragmentation_in_percent > 10
   AND ix.name IS NOT NULL