I've made up a new SQLServer db with about 95% of our production data as a test. I then dropped all the records and then filled the tables again with 100% of our data. The db seems fine.
I thought I'd look at the index frag because of the record drop and re-add and it was high in a few tables, >70%. I tried to do a rebuild and a re-organise but the frag level is same. These are primary keys, non-clustered.
I noticed the fragmentation is high only on the tables with not many records, < ~1000, and the frag on table with many records, > 50000, is fine, like about 0.2%. So are tables with not many records likely to have high frag?
I've tried rebuild and reorder in the SMSS (right click on index, rebuild) and also in code:
ALTER INDEX ALL ON [dbo].[log_GRSPN]
REBUILD WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
SORT_IN_TEMPDB = OFF,
ONLINE = OFF )