0

This question already has an answer here:

I have been trying to gather information about rebuilding and reorganizing indexes. From stackoverflow page (How Often should the indexes be re-build in sql-server DB?) i got this query :

SELECT 
    t.NAME 'Table name',
    i.NAME 'Index name',
    ips.index_type_desc,
    ips.alloc_unit_type_desc,
    ips.index_depth,
    ips.index_level,
    ips.avg_fragmentation_in_percent,
    ips.fragment_count,
    ips.avg_fragment_size_in_pages,
    ips.page_count,
    ips.avg_page_space_used_in_percent,
    ips.record_count,
    ips.ghost_record_count,
    ips.Version_ghost_record_count,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.avg_record_size_in_bytes,
    ips.forwarded_record_count
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN  
    sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN  
    sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
    AVG_FRAGMENTATION_IN_PERCENT > 0.0  
ORDER BY
    AVG_FRAGMENTATION_IN_PERCENT, fragment_count

The problem is that when i rebuilt indexes the avg_fragmentation_in_percent increased instead of decreasing. Any pointers?? If this is the normal behavior then what am i missing here??

Previously the avg_fragmentation_in_percent was 30 and after rebuild it has increased to 66.

enter image description here enter image description here enter image description here enter image description here

marked as duplicate by Mark Storey-Smith sql-server Jul 13 '14 at 11:59

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Your table is tiny at 91 rows, stop worrying. See the linked question & answer for an explanation as to why you fragmentation remains on tables this small. – Mark Storey-Smith Jul 13 '14 at 12:02
  • Hi Mark how do you qualify a question as duplicate what if user searched website but was not able to find solution, in that case he posted he question. Instead of marking this as duplicate can we just not post the link as an answer to this thread and ask user to have a look at it. I am new to this community so please consider this as suggestion. I am also aware this is not correct place for suggestion but noetheless :) – Shanky Jul 13 '14 at 12:24
  • @Shanky The duplicate feature exists so we don't have to do what you are suggesting. – Mark Storey-Smith Jul 13 '14 at 12:49
3

As per followed practice you can rebuild index when fragmentation is >30 % and can reorganize when fragmentation is between 10 and 30 %. You should also include page_count value in your query. If page_count value( which is there in DMV sys.dm_db_index_physical_stats) is <1000 you don't need to rebuild or reorganize such indexes even though they show large fragmentation. I have seen sometimes for such indexes when rebuilt index fragmentation increases but it would hardly matter.

In your case page_count is 3 so absolutely no need to rebuild or reorganize index.

I would also like to see your full rebuild script it it quite possible you may have used less value of fill factor to rebuild index that would cause more number of pages after rebuilt and thus increasing fragmentation.

Below is a article written by Bob Dor from Microsoft where he actually pointed out that due to MAXDOP setting when used during index rebuild can cause increase in fragmentation I am sure it is not applicable to your environment but have a look: http://blogs.msdn.com/b/psssql/archive/2012/09/05/how-it-works-online-index-rebuild-can-cause-increased-fragmentation.aspx.

Please use below query and then find out indexes which needs rebuild:

select
    ips.index_type_desc,
    ips.index_depth,
    ips.index_level,
    ips.avg_fragmentation_in_percent,
    ips.fragment_count,
    ips.page_count,
    ips.avg_page_space_used_in_percent,
    ips.record_count
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN  
    sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN 
    sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
    AVG_FRAGMENTATION_IN_PERCENT > 0.0 and page_count >1000 
ORDER BY
    AVG_FRAGMENTATION_IN_PERCENT, fragment_count
  • Thank you.. I think i was missing the page count thing.. – user2438237 Jul 13 '14 at 20:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.