Reorganize or Rebuild Indexes

Rédigé par M Sozezzo - - Aucun commentaire

This script remedy index fragmentation by reorganizing or rebuilding an index.

Small tables, small indexes, low fragmentation, we do not care, and keep simple & easy

We are talking about the guidance which is:
•if a table has less than 10000 rows, to do nothing
•if an index has less than 1000 pages, to do nothing
•if the index has:
    less than 5% logical fragmentation, to do nothing
    between 5% and 30% logical fragmentation, reorganize it
    more than 30% logical fragmentation, rebuild it
• New FILLFACTOR = 98%


Why do we fix something we do not need it?
We should start to delete unused indexes if it is possible: [see Unused indexes]
•Unused index, delete it, when :
    - Nonclustered indexes
    - Non-primary keys
    - Non unique
    - Non-unused  (#Total read < 100 and #total write > 1000 )
    - Hypothetical Index.

 

Alternative solutions:
1. We can buy an application for index monitoring and analysis.
2. We can change the configuration. ex: MIXED_PAGE_ALLOCATION
3. We can create better indexes.
4. We can use this nice solution of Michelle Ufford [https://github.com/MichelleUfford/sql-scripts/tree/master/indexes]

 

 

Lire la suite de Reorganize or Rebuild Indexes

Fil RSS des articles de ce mot clé
PHP Warning: include_once(plugins/simplepie/simplepie.inc): failed to open stream: No such file or directory in C:\Inetpub\vhosts\sozezzo.com\httpsdocs\blog\plugins\simplepie\plx.simplepie.php on line 2 PHP Warning: include_once(): Failed opening 'plugins/simplepie/simplepie.inc' for inclusion (include_path='.;.\includes;.\pear') in C:\Inetpub\vhosts\sozezzo.com\httpsdocs\blog\plugins\simplepie\plx.simplepie.php on line 2