Search code examples
sql-server-2005defragmentation

Defragmenting SQL indexes

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:

http://blog.sqlauthority.com/2008/03/04/sql-server-2005-a-simple-way-to-defragment-all-indexes-in-a-database-that-is-fragmented-above-a-declared-threshold/

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.

Solution

  • If there are not a lot of rows in the table, or the data does not consume a page of data (8k), you will notice fragmentation of indexes even after rebuild.