Check index fragmentation

Select dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
from sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) as indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes as dbindexes on dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
where indexstats.database_id = DB_ID()
order by indexstats.avg_fragmentation_in_percent desc



Index_ID of 0 indicates the "heap" for that table.  It is where the table data is stored.  Index_ID of 1 indicates that there is a clustered index on the table.
A heap does not have a name that's why it is NULL.


I would Rebuild Indexes / Re organize indexes only if 
Avg fragmenation >10
Re organize when frag < 30.0
Re build when frag > 30.0

Page count >25
IndexID>0