February 9, 2009 at 6:10 pm
Hey I have a sproc that runs dbcc showcontig against all the indexes in my database and shows me the ones that have a logical frag percentage over 15%.
I have a similar script that runs dbcc indexdefrag based off those same results (basically modified the showcontig script to execute indexdefrag).
I also have a script that runs dbcc dbreindex based on the same parent script.
I noticed one particular index was at about 33% fragmentation.
I had read somewhere that dbcc indexdefrag was going to be replaced eventually by alter index re-organize and that dbreindex would be replaced by alter index rebuild.
So along those lines I set out to do some experimenting. I ran dbcc showcontig on one table, and it showed 33% logical fragmentation on one of the indexes. So I ran the alter index rebuild on that index, then did another showcontig. Some how or another, the index now shows 75% fragmentation? Am I missing something? The default fill factor on the database is 75% and I show that the pages are about 69% full. Should I be using something other than showcontig? Under the index properties, it's showing 75% total fragmentation as well, so I figure that's accurate.
February 9, 2009 at 7:01 pm
Run sys.dm_db_index_physical_stats and look for "avg_fragment_size_in_pages" that wold be the best reading I would say
February 9, 2009 at 9:26 pm
How many pages in that index? Small tables will always show some level of fragmentation. If this is a larger table (1000 pages or more) and it still shows 75% fragmentation, I would look at how much space is available in the data file.
If there isn't enough space available to rebuild the index using contiguous storage - you could end up with a fragmented index. However, I would not expect it to show up that fragmented.
And finally, make sure you don't have auto-shrink enabled - as shrinking the data file will fragment the indexes after all the work has been done to rebuild them.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 10, 2009 at 12:37 pm
Sure enough there are only 4 pages in this index. I'm not sure why it wouldn't re-order them, seems like if you were going to re-organize an index you would fix that and order them 1-2-3-4, rather than 1-2-4-3 or something.
February 10, 2009 at 1:30 pm
Well, with that small of an index - it will always show some level of fragmentation. It really doesn't mean much anyways if you think about it. With only 4 pages - the whole index/table will be loaded into memory anyways, so why worry about fragmentation?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply