Ive got a large transactional table. its has a clustered index on the 'rowID' column (bigint), which is in great shape (as data isnt ever deleted from the db, but thats another issue for another day).
the non-clustered index on 'fkId' and 'trnDate' is horribly fragmented, at ~90%.
I rebuild it, and the resulting fragmentation is still ~90%. I tried again adjusting the fill-factor and its higher yet. interesting, and the table DOES have 4 'bigint' columns in it. pages show avg free space of 789Kb (which is about 10%, so good to work with)
I am getting this from dm_db_index_physical_Stats, and comparing with results of dbcc showcontig. for good measure I ran a dbcc updateusage (which I know I shouldnt need on sql 2008, but...)
everything I've searched on along these lines leads me to generic stuff about tables with few pages, (this one has over 200k pages) and not quite what im after.
any useful links or tips?
thanks in advance!