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
DatabaseSelect scripts to run it).
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: sql server, index, rebuild, ola hallengren
posted @ Wednesday, March 04, 2009 3:29 PM