开发者

Table with no more than 30k records needs index rebuilding after a handful of inserts

I have a table with 20 or so columns. I have approximately 7 non-clustered indexes in that table on the columns that users filter by more often. The active records (those that the users see on their screen) are no more than 700-800. Twice a day a batch job runs and inserts a few records in that table - maybe 30 - 100 - and may update the existing 开发者_C百科ones as well.

I have noticed that the indexes need rebuilding EVERY time that the batch operation completes. Their fragmentation level doesnt go from 0-1% step by step to say 50%. I have noticed that they go from 0-1% to approx. 99% after the batch operation completes. A zillion of selects can happen on this table between batch operations but i dont think that matters.

Is this normal? i dont think it is. what do you think its the problem here? The indexed columns are mostly strings and floats.


A few changes could easily change fragmentation levels.

  • An insert on a page can cause a page split
  • Rows can overflow
  • Rows can be moved (forward pointers)

You'll have quite wide rows too so your data density (rows per page) is lower. DML on existing rows will cause fragmentation quite quickly if the DML is distributed across many pages

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜