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
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?