Comments
Sort by recent activity
Nigel
the following statement was ran against a database that flagged in SQL Response as having high fragmentation.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES, FAST
I found many tables that reported high fragmentation and picked one of these at random.
The following T-SQL made no difference to the logical fragmentation
ALTER INDEX ALL ON DAA_Adequacy REBUILD
i then tried specifying the fillfactor rather than use the sql server default (set to 0)
ALTER INDEX ALL ON DAA_Adequacy
REBUILD WITH (FILLFACTOR = 80)
this made a difference of about 10 dropping from 87.5 to 77.77777778
not a substantial drop and may not be enough to stop sql response picking it up again. If you would like the output from dbcc showcontig please could you supply me your email address. I would appreciate your advice
Regards
Perry / comments
Nigel
the following statement was ran against a database that flagged in SQL Response as having high fragmentation.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES, FAST
I found many tables that rep...
Nigel
upon further investigation i think we may need to use a different fill factor to ensure that pages have ample free space for the indexes to grow. What is the threshold value sql response is comparing too for fragmentation alerts too occur, i can get the logical frag down to 26 by specifying the fill factor leave 70% of each page free but i dont want to go to that extreme, Thank you for your help
Regards
Perry / comments
Nigel
upon further investigation i think we may need to use a different fill factor to ensure that pages have ample free space for the indexes to grow. What is the threshold value sql response is c...
Hi Nigel
i work alongside Mark and have read your reply but the following query has now arisen.
i rebuilt all the indexes on one of the tables reporting a high logical fragmentation when running the T-SQL
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES, FAST
I tried using a fillfactor of 85 (instead of SQL instance default of 0) and whilst this did make a difference it was very minimal (from 87.5 down to 77.66), what is the size value you are using to determine fragmentation?
DBCC SHOWCONTIG now uses a different algorithm as opposed to SQL2000 and the way fragmentation is calculated has changed drastically, the values will always appear higher. Is this something that the software takes into account?
It may be that we need to re visit our settings for fillfactor usage but it doesnt seem to have a huge impact, do you have any recommendations?
Regards
Perry / comments
Hi Nigel
i work alongside Mark and have read your reply but the following query has now arisen.
i rebuilt all the indexes on one of the tables reporting a high logical fragmentation when running th...
i will get those details and post back, in relation to my original question is the application taking into account the algorithm changes in SQL2005? / comments
i will get those details and post back, in relation to my original question is the application taking into account the algorithm changes in SQL2005?
rather than backup across the network, backup to local drive and use the "copyto" parameter to copy the file to the network / comments
rather than backup across the network, backup to local drive and use the "copyto" parameter to copy the file to the network