Index Rebuild, Re-Organise or Just a Statistics Update? Use "IndexOptimise"!

UPDATE: Ola has moved his site to http://ola.hallengren.com - please check there for the latest versions of his IndexOptimise (and other) scripts).

Ola Hallengren recently posted an all-in-one solution for backups, integrity checks and index optimisation for SQL Server 2005 and 2008 here (http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html).

I'm particularly interested in Ola's extremely flexible index optimisation stored procedure, IndexOptimise (you also need his CommandExecute and DatabaseSelect scripts to run it).

By default, IndexOptimise rebuilds indexes with fragmentation over 30%, re-organises indexes with fragmentation between 5% and 30%, and ignores indexes with fragmentation under 5% or smaller than a configurable size. The level of configurability offered improves on Maintenance Plans - see Ola's comparison here - because changes to indexes that don't need changing increase differential backup sizes (and I'm always confused about which to use...a rebuild, re-organise or just a statistics update?)

Ola has also gone to the trouble of documenting his solution and creating a 1-script install.

Well done Ola! (via Paul Randall)

Tags: , , ,

posted @ Wednesday, March 04, 2009 3:29 PM

Print
Comments have been closed on this topic.
«April»
SunMonTueWedThuFriSat
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910