| Author |
Topic |
|
gazzer 2004
Starting Member
12 Posts |
 Posted - 2010-07-14 : 04:08:40
|
| Hi Guys, just thought i'd drop you a line as i am having difficulty in figuring out Reindexing and fragmentation results.Ok here goes.I checkd the fragmentation levels on one of our DB's and as far as i can tell some of the indexes are fragmented, I used the code taken from the sql 2005 books online as seen below.SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);GOThe results show that a lot of the indexes have upwards of 60%+ for the column avg_fragmentation_in_percent which I take to mean these indexes are fragmented.Is that correct can i assume that there is fragmentation of indexes from the results above?So with that in mind i tried to sort this out, using various methods.I set up a maint plan and used the rebuild indes task to sort it out - Had no effect, still shows same % fragmentation.So next I tried the books online code and still no effects the % stayed the same.I have used code from the interweb, and still the % stays the same.My question is this, with all these different methods being used, why does the fragmentation levels never change, I thought I would see a dramatic change in the %, but that is not the case.Am I missing something or doing something wrong here?Sorry for this long winded post. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-14 : 04:35:15
|
| If the indexes have relatively few pages they will never get to 0% fragmentation. Could that be it? |
 |
|
|
gazzer 2004
Starting Member
12 Posts |
Posted - 2010-07-14 : 04:53:02
|
| Hi well thats the thing I don't really know that much about fragmentation results so it could be.Ok I have run code again.One particular index is showing avg_fragmentation_in_percent of 83.33 % and a page_count of 10, another is showing 80.61% with a page_count of 98, and yet another showing 90.09% with a page count of 11, so what would be a good and bad page.It's so confusing.Cheers |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-14 : 05:06:08
|
| I can't remember the cutoff. I think I have heard people say that 100 pages is "too small to defragment", so on that basis all your tables are too small to worry about.We treat all tables with less than 1,000 pages as "small" in our defragmentation policy (we just rebuild them, rather than defragment them, and only do them once a week rather than when their fragmentation percentage is "high") |
|
|
|
gazzer 2004
Starting Member
12 Posts |
Posted - 2010-07-14 : 05:47:18
|
| Thats good info to remember, in that case I don't see any issues with our database at the moment, as i dont see any pages near that number.If anyone else has input I would appreciate all I can get.Cheers |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-14 : 13:17:26
|
quote:
Originally posted by tkizer
The cutoff is 1000 pages. Ignore indexes with less than 1000 pages as they can't be defragmented.
Thanks Tara. You wait until you are my age and you can't remember what day of the week it is  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-14 : 13:37:36
|
| "Wait until you've been pregnant,"Medical science will be impressed - if nothing else! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Fragmentation a puzzler
Tara Kizer