Though SQL server provides user friendly tool to create rebuilding tasks , these tasks need to be run only once a week during weekends if posssible when the load on server is ,low.

Also Iam not great fan of buliding indexes in autmated way, rather do it manually based on scripts below which has been written by SQL experts , if you are bent upon automating them you need to customize them further.

- In my checklist of weekly activity run fragmentation check for tables have database pages greater than 10,000 as described below and fragmentation greater than 30% in SQL 2005

SELECT name,page_count,object_id,index_type_desc,AVG_FRAGMENTATION_IN_PERCENT FROM

sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') a,

sysdatabases b

WHERE AVG_FRAGMENTATION_IN_PERCENT > 30

AND a.database_id = b.dbid

AND page_count >10000

-- If AVG_FRAGMENTATION_IN_PERCENT 30% use rebuild with online option

index to reduce fragmentation

ALTER INDEX [CL_FindAndFixFragmentation_Index] ON FindAndFixFragmentation

REBUILD WITH (FILLFACTOR = 90, ONLINE=ON)

🙂