Thursday, February 18, 2010
SQL Server maintenance solution
I've become a big fan of Ola Hallengren's SQL Server 2005 and 2008 database maintenance solution since I discovered it a few months ago. As anyone that has delved very far into the Reorganize and Rebuild Indexes tasks in SQL 2005/2008 maintenance plans knows, they couldn't exactly be described as 'sophisticated'. The tasks do not take the existing fragmentation level in the indexes into account - they simply fire off a rebuild or reorganize of all indexes. The general recommendation for maintaining indexes is to reorganize if fragmentation is between 10% and 20%, and to do a rebuild if fragmentation is greater than 20%. Doing rebuilds of indexes with little to no fragmentation is a waste of CPU cycles. Those who want a more intelligent method of maintaining their indexes are forced to go at it programmatically. I had been mulling this exact same thing over when I stumbled across Ola's free solution. It includes solutions for index maintenance, backups, and integrity checking. Definitely check it out here.
Posted by M. W. Albert at 7:00 PM