SQL Index what to Use, Rebuid or reorganize index task?

FCP

What's the best practice.

To make an maintenace plan using Rebuid or Reorganiza index? or none?

And will the SIFT table be affected?

Thanks

kriki

[Topic moved from Navision forum to SQL General forum]

kriki

-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 (I think), otherwise it means that the table is blocked during the rebuilding. Statistics are recalculated.
-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!

-If you don't have special tools to program it, best use rebuild index once a week (e.g. Sunday) and if needed, you can use defrag/reorganize during the night on weekdays. 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-tables are normal tables for SQL, so they will also be rebuild or defragged. The 0-SIFT-records will NOT be deleted.

-PS: best client-upgrade to 4.00SP3 upgrade 6 or 5.00 + hotfix (and disable indexhinting in both cases) to get best performance out of your SQL-box.

FCP

First of all "Grazie" for that good answer.
Do you think a reasonable solution would be:

tuesday,thursday
- reorganize Index
- recalculate statitistics

on Saturdays
- rebuid index

---

And about 0-SIFT-records what to do? Is it really an important issue?
should I: http://www.mibuso.com/forum/viewtopic.php?t=17035

I've an 4.00SP3 no Hotfix's

The upgrade 6 or 5.00 + hotfix I was afried to install it (Is it easy to disable indexhinting, after you install it)

kriki

I would do a reorganize index+recalculate statistics every day (except when you do a rebuild index off course).

0-SIFT : If you want to delete them, you must install upgrade 6 otherwise better don't do it. It can become a factor for performance if you don't do it.

Ola_Hallengren

You could also do this dynamically. Microsoft has some recommendations about fragmentation levels and when to do a reorganize and when to do a rebuild.
http://msdn2.microsoft.com/en-us/library/ms189858.aspx

This means that you can rebuild indexes with high fragmentation, reorganize indexes with medium fragmentation and do nothing about indexes with low fragmentation. You can also choose to update statistics on the indexes where you do a reorganize.

I have a stored procedure that could help you with this. It's being used today on a very large Navision installation.

http://ola.hallengren.com/sql-server-in ... nance.html

Ola Hallengren
http://ola.hallengren.com