I have been trying to defrag indexes in SQL Server 2005 and nothing seems to work. I have created multiple Maintenance Plans using the wizard but the job always fails. I have run the script from this site, which is originally from Microsoft:
Even if I go to the specific table in Object Explorer and select the Indexes folder and select Rebuild All the fragmentation % never changes, even though it reports as completing as successful.
Shouldn't a rebuilt index have 0% fragmentation? If so why would this sql not work:
ALTER INDEX [IndexName] ON [dbo].[TableName]
REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = Off,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
This is the sql generated by selected Rebuild Index.