Am gasit un best practice aici:
http://technet.microsoft.com/en-us/library/ms189858.aspxPe scurt, aflu gradul de fragmentare:
SELECT object_name(S.object_id), I.[name], S.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(@tableName), OBJECT_ID(@tableName), NULL, NULL , NULL) S
INNER JOIN sys.indexes I ON I.object_id = S.object_id AND I.index_id = S.index_id
Dc. gradul de fragmentare > 5% si < 30% trebuie facut ALTER INDEX REORGANIZE (doar sunt re-ordonate leaf level pages)
Dc. gradul de fragmentare > 30% trebuie facut ALTER INDEX REBUILD (adica indecsii sunt recreati).
Multumesc tuturor pentru sugestii, suport si idei.