Fragmentation of Index occurring frequently

Question

Hi,

I have a set of tables (4 in precise) on which transactions happen frequently(DML). As a part of the performance enhancement, i have de-fragmented all the indexes on these tables. Thereafter my queries started running faster for few hours. Unfortunately, the indexes were again fragmented and average_fragmentation_in_percent got up-to 90%  by end of the same day.'What would be the reason?

Please suggest.

Note: We haven't shrinked the database.

-Charan.

Answer 1

You need to set an ideal value for the fill factor,  please read this link 

http://www.sqlmag.com/blog/beginning-sql-server-one-step-at-a-time-43/net-framework/what-is-the-best-value-for-the-fill-factor---index-fill-factor-and-performance--part-2-139448

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 2

As mentioned, the fill factor may solve your issue.  However if you post your DDL and example DML then there may be other changes that can help. 

Answer 3

Reason is you table get very frequently the insert ,Delete commends so the table is very quickey get fragmented to over come this introduce fill factor. but fill factor also helps your for two to three days in your senerio. but your de-Fragmentation work will be minimized here

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 4

Also ensure that you do not do any SHRINKFILE on the database post Index Rebuilding. By the way, what version of SQL are you using? If > 2005, are you using sys.dm_db_index_physical_stats to check for degragmentation and how are you rebuilding the indexes (ALTER INDEX/ DBCC Commands)

Answer 5

Thank you all for the responses.  I'm using SQL Server 2008 R2. I'm using ALTER INDEX --- REBUILD command to de-fragment the indexes. And i remember not  to use DBCC SHRINK operations post re-indexing.

Answer 6

Where is the defragementation. Is it at the leaf level or the intermediate levels?

Answer 7

Defragmented at the index 0 level, which is root level.

Answer 8

Just one off question. Is the clustering key a UNIQUEIDENTIFIER by any chance?

Answer 9

Nope...its a varchar column

Answer 10

Fragmentation happens when a new row or updated version of an existing row does not fit on the page on which it has to go.

Fragmentation caused by inserts can happen if you have meaningless (semi)random index key, such as a uniqueidentifier. If this is the case, you could consider replacing with an Identity value with appropriate size (int or maybe bigint).

Fragmentation caused by updates point to an inappropriate fill factor for the index, and can be countered by increasing the fill factor.

Please not that (from a performance point of view) fragmentation is meaningless for tables with just a few hundred pages.

---

Gert-Jan