RS First Dynamics NAV Blog

...from NAV 3.60 to NAV 2013
Archivio Posts
Anno 2015

Anno 2014

Anno 2013

Anno 2012

Anno 2011

Anno 2010

Anno 2009

Anno 2008

Anno 2007

SQL 2005 Best Practice Indexing Tuning

Rebuild Index:  
Throws away the complete index and rebuilds it from scratch.
This can be a long process but it is more complete then defrag/reorganize index.
Best used if the fragmentation is heavy (over 30%).

Can be done online in SQL2005 enterprise edition otherwise it means that the table is blocked
during the rebuilding.
Statistics are recalculated.

Defragment\Riorganize Index:
- Defrag/reorganize index : is faster then rebuild index but less complete.
You can use it when index fragmentation is under 30%.
Can be done online, meaning people can use the table when doing it.
Statistics are NOT recalculated

Sift Index Table
SIFT-tables are normal tables for SQL, so they will also be rebuild or defragged.
The 0-SIFT-records will NOT be deleted.
--> SSI utility delete 0 Sift table

Best use rebuild index once a week (e.g. Sunday)

You can use defrag/reorganize during the night on weekdays or allfdays
--> If you use defrag, you also need to recalculate the statistics.
Of course the maintenance plans you make vary according to DB-size and time-windows
when you can do the actions.

Recalculate statistics is best done every night (except in case of a rebuild index).

Sift Index Table
All nights

SiftTable 0 delete
Reindex Full  

Categoria: Sql Server ALL
venerdì, 08 feb 2008 Ore. 16.54

Messaggi collegati

  • Views Home Page: 453.774
  • Views Posts: 866.902
  • Views Gallerie: 0
  • n° Posts: 343
  • n° Commenti: 0
Copyright © 2002-2007 - Blogs 2.0 | Home Page Blogs
ASP.NET 2.0 Windows 2003