Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
panaymel
Starting Member
2 Posts |
 Posted - 2007-02-25 : 22:29:05
|
| Hi,I have recenlty had to rebuild the indexes for the entire database as they were running a little slow. It prompted me to further investigate the health of the indexes on the database. I have been using the sys.dm_db_index_physical_stats view to do this. I had a query with regards to the avg_fragmentation_in_percent column that the view displays. I have rebuilt all the indexes and for some of the indexes the fragmentation value remains the same. The following is an example of what is returned by the sys.dm_db_index_physical_stats for a single index:Table, IndexName, avg_fragmentation_in_percent, avg_page_space_used_in_percentLinks, PK_Links, 77.7777777777778, 97.1501606127996Links, PK_Links, 0, 1.42080553496417I do not understand why this index the fragmentation is so high even though I have rebuilt it. It is not just this index it occurs for other indexes as well. What could be some of the factors that are causing this to occur? |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-26 : 06:07:33
|
| "What could be some of the factors that are causing this to occur?"Small tables? I've found they tend to have poor percentage figures, probably because the numbers involved are so small! Anyway, for small tables it isn;t going to make much difference whether they are rebuilt or not, all the pages will fit into memory easily.The other possibility that occurs to me is that perhaps the Fill Factor is very low, or the key size is very bigKristen |
 |
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-02-26 : 11:20:25
|
| The other thing I can assure you is to rebuild the indexes with lower fill factor say 50% once and then re-rebuild them with the original fill factor or 10% free space, it will work.this plagued me a long time at the beginning of hte month. In fact one of the questions that was asked of me by an interviewer. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
panaymel
Starting Member
2 Posts |
Posted - 2007-02-26 : 20:19:08
|
quote:
Originally posted by Kristen
"What could be some of the factors that are causing this to occur?"Small tables? I've found they tend to have poor percentage figures, probably because the numbers involved are so small! Anyway, for small tables it isn;t going to make much difference whether they are rebuilt or not, all the pages will fit into memory easily.The other possibility that occurs to me is that perhaps the Fill Factor is very low, or the key size is very bigKristen
Small tables seems to have something to do with it as to does the fill factor. There are only 2516 records in the table that the index is on. I rebuilt the index with different fill factors. I found that the lower the fill factor the lower the avg_fragmentation_in_percent became. I decided to bulk load some records into the table to increase the row count from 2516 to 21976 and then rebuilt the index. The avg_fragmentation_in_percent went to 0. I then decided to test rebuilding the index with different fill factors only to discover that with more records in the table the fill factor did not affect the avg_fragmentation_in_percent value. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-27 : 04:35:17
|
| I have never done any tests, but that certainly mirrors my findings.I just took the view that if the table had very few records I didn't care what the fragmentation was!Our rebuild process has a check for fragmentation, if the table is less than X pages we do a rebuild, regardless, and if it is more than that we do a DEFRAG if the fragmentation is greater than YSet X and Y as you see fit!Kristen |
|
|
|
|
|
|
|
|
Rebuilding Indexes