Defragmenting SQL indexes

Question

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.

Answer 1

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.

Answer 2

Although possibly related with a very small database as suggested in other response, the problem is more likely tied to the FILLFACTOR.

The ALTER INDEX statement shown does not explicitly mention the FILLFACTOR, and hence the rebuilt is done on the basis of the current fillfactor value, leaving a fragmentation that should approximate this factor. (It is rarely an exact match because a given index entry cannot be split between two nodes, hence making each node possibly leave more or less room than the fillfactor would require; indeed in some cases, this would require an fractional number of bytes... but let's not stray from the real issue...)

You can inquire the current fillfactor value for a given index by looking in the sys.indexes table, with a query similar to SELECT * FROM sys.indexes WHERE object_id IN ( SELECT object_id FROM sys.objects WHERE name = 'myTableName')

Alternatively if you run a modified version of the ALTER INDEX snipped shown, where ... FILLFACTOR = 100 ... is added in the "WITH" option, I suspect the fragmentation reported will match your expectation.

For sake of clarity, the PAD_INDEX option only instructs SQL to leave some space in the intermediate nodes of the index, these therefore will not incur any "fragmentation", but the leaf nodes will.

This said... It may be a good idea to leave a certain amount of fillfactor, in order to delay in onset of fragmentation as new data is inserted into the table.