Paul Randal has a good blog post about this.

http://blogs.msdn.com/sqlserverstorageengine/archive/2007/06/07/what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed.aspx

About the index optimization stored procedure it outputs things like this to the output file.

DateTime: 2008-03-05 21:58:59

Command: ALTER INDEX [IX_Address_StateProvinceID] ON

[AdventureWorks].[Person].[Address] REORGANIZE

Comment: IndexType: 2, LOB: 0, PageCount: 1001, Fragmentation: 7.40741

DateTime: 2008-03-05 21:59:10

Please try it out and let me know if you have any questions.

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Ola Hallengren

http://ola.hallengren.com