Need for Reorganize/Rebuild indexes after frequent DELETE?

 

I have a table with 40 million rows (and growing) with an identity primary key clustered index and a non-clustered index.

I often need to Delete old or obsolete information.

Because this is a frequent action, should I care about fragmentation and plan to take some maintenance on the indexes to keep performance?

What should be, or how should I evaluate the best action on both indexes:

REBUILD or REORGANIZE?

The question you need to ask is whether index fragmentation is actually causing you any issues.

I'd consider it a good idea to have some sort of index maintenance script. If you use Ola Hallegren's scripts then you can set the thresholds for a rebuild/reorganise (say over 50% fragmentation for a reorganise, over 80% for a rebuild) and this can run overnight out of hours. You can also set a threshold so that it only considers indexes over a certain size so you're not doing unnecessary rebuilds on tiny indexes.

Here's a link to said scripts;

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

With this number of rows and these operations Yes you should care about fragmentation because performance may reduced. Based on several articles I've read I implemented a defragmentation of indexes like this :

  • If fragmentation is less than 5% do nothing
  • If fragmentation is between 5 and 30% do a Reorganize
  • If fragmentation is greater than 30% do a Rebuild

    • You can get more details on fragmentation of indexes here : http://blog.sqlauthority.com/2010/01/12/sql-server-fragmentation-detect-fragmentation-and-eliminate-fragmentation/ and here http://www.schneider-electric.com/en/faqs/FA234246/

    You can analyse the evolution of index fragmentation and then opt for a defragmentation every nights or once a week (or other frequence)

    链接地址: http://www.djcxy.com/p/95684.html

    上一篇: 在列存储索引表上重新创建索引，包含350亿行

    下一篇: 频繁删除后需要重新组织/重建索引？