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?
Best Answer
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.