Database Indexing - Maintenance Jobs

Question

I have created a script that runs every night to rebuild & re-organize indexes based on the fragmentation, Indexes with Fragmentation > 30% are rebuilt, Indexes with Fragmentation 10% - 30% are re-organized.

After running the script I noticed we have about 400 indexes that are still reflecting a fragmentation count of > 10%, and after more investigation i came across some posts which mentioned any indexes with a page count < 1000 would not be re-organized by SQL.

I investigated further why my script was not updating all my indexes and found out that the results from the query

sys.dm_db_index_physical_stats(DB_ID(DB_NAME()),NULL,NULL,NULL, 'DETAILED')

show page counts of 5, 6 for tables that have > 1 mill records in, the record_count column had about 16 000 records, I then forced an index rebuild on the index by running

ALTER INDEX [indexname] ON [schema].[table] REBUILD WITH (FILLFACTOR = 85, STATISTICS_NORECOMPUTE = OFF)

and my index now shows 1 mill + record count.

My question is: why would a re-organize of he index not correct the record count in the index and the fragmentation & what would have cause this record count value to become so out dated on the index in the first place?

My plan now is to force a rebuild of all indexes tonight & then run my script as per normal going forward. Should I worry about the index record count becoming out dated again?

Answer 1

Reorganizing an index does not update the statistics on an index. Reorganize will ensure that the B+ tree is balanced, since SQL Server doesn't auto balance trees during operation. Rebuilding an index will recreate the index, allowing the engine to update the statistics on an index.

You should consider periodically reorganizing and updating statistics on lightly fragmented queries granted you can spare the maintenance time. You can also try to update statistics periodically using samples, if your data is homogenous enough.

You can read more about Update Statistics here

http://msdn.microsoft.com/en-us/library/ms187348.aspx

Kimberly Tripp blogs heavily on index maintenance on http://sqlskills.com

Check out her links for Statistics and Indexes.

Answer 2

Thanks for your answers, I ended up using a script from there

http://ola.hallengren.com/

Must have been something that my script was not catering for e.g. xml columns all is working great now.

Answer 3

Sometimes the index and/or statistics are not up-to-date and SQL Reindex Jobs are thus unable to determince if the index is fragmented or not.

Running DBCC UPDATEUSAGE beforehand will ensure that the indexes are then recreated or reorganized according to fragmentation level.