How to find the reason of Fragmentation ?

AUTOSHRINK setting on database may cause index fragmentation.

You can decrease index fragmentation if you use FILLFACTOR on dynamic tables (lots of INSERTs):

http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/

Use FILLFACTOR 70 (30% empty space) on extreme dynamic tables.

Use FILLFACTOR 80-90 on dynamic tables.

Optimization article:

http://www.sqlusa.com/articles/query-optimization/

BOL: "A correctly chosen fill factor value can reduce the potential for page splits by providing enough space for index expansion as data is added to the underlying table.".

http://msdn.microsoft.com/en-us/library/ms177459(v=sql.90).aspx

---

Kalman Toth SQL SERVER 2012 & BI TRAINING

New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012