• I pulled these scripts from the Microsoft 70-431 training kit:

    This script allows you to view the fragmentation

    DECLARE @dbname VARCHAR(20)

    SET @dbname = 'AdventureWorks'

    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(@dbname),null,null,null,'DETAILED')

    WHERE index_id <> 0

    )as dt --it does not return info about heaps

    INNER JOIN sys.indexes si

    ON si.object_id = dt.object_id

    AND si.index_id = dt.index_id

    --If avg_fragmentation_in_percent > 10 then the database has External Fragmentation

    --If avg_page_space_used_in_percent < 75 then the database has Internal Fragmentation

    This script determines if you should rebuild or reorganize based off the authors

    recommendation. You can adjust the values.

    DECLARE @dbname VARCHAR(20)

    SET @dbname = 'AdventureWorks'

    --These indexes should be reorganized, not rebuilt

    -- Alter Index ... Reorganize

    select *

    from sys.dm_db_index_physical_stats (db_id(@dbname),null,null,null,'DETAILED')

    where avg_page_space_used_in_percent 10 and

    avg_fragmentation_in_percent < 15

    --These indexes should be rebuilt, not reorganized

    -- Alter Index ... Rebuild

    select *

    from sys.dm_db_index_physical_stats (db_id(@dbname),null,null,null,'DETAILED')

    where avg_page_space_used_in_percent 15