none
Index still fragmented after rebuild RRS feed

  • Question

  • I have a database 2008R2 with 97% of average fragmented index and page counts = 164785 and the index table is more than 300,000 rows.

    I use the ola.hallengren script to optimize index and the stat above provided by the IndexCheck.sql script.

    My question is why after the index fragmentation above has been rebuilt the fragmentation is very much the same as before. The indesc has been rebuilt because it's lager than 30% and page count is larger than 1000 .

    However, if a copy of that database has been restored on a different server, the fragmentation is much below than 30%.

    Tuesday, January 20, 2015 2:47 AM

Answers

  • Hi NuocNho,

    According to your description, you want to know why fragmentation almost reaches back to same level after the index rebuilding. This issue could happen due to the huge DML operations after rebuilding index, or the inappropriate method of rebuilding index, such as online index rebuild.
     
    To troubleshoot your issue, I recommend you to use one or more of the methods below
     
    1.Use offline index rebuild if you can.
     
    2. Set 'max degree of parallelism'  to 1. For how to configure 'max degree of parallelism', please refer to the article: https://msdn.microsoft.com/en-us/library/ms189094.aspx#TsqlProcedure
     
    3.Redefine some of the clustered indexes.

    Regards,
    Michelle Li

    Thursday, January 22, 2015 2:12 PM
    Moderator

All replies

  • Hallo NuocNho,

    can you please post the result of this query? Please replace the table name with your table name!

    USE <YourDatabase;
    GO
    
    SELECT	I.name,
    	I.is_primary_key,
    	I.is_unique,
    	DDIPS.index_depth,
    	DDIPS.index_level,
    	DDIPS.fragment_count,
    	DDIPS.page_count,
    	DDIPS.record_count,
    	DDIPS.avg_fragment_size_in_pages,
    	DDIPS.avg_fragment_size_in_pages,
    	DDIPS.avg_page_space_used_in_percent
    FROM sys.dm_db_index_physical_stats
    (
    	DB_ID(),
    	OBJECT_ID('<YourTable>', 'U'),
    	NULL,
    	NULL,
    	'DETAILED'
    ) AS DDIPS INNER JOIN sys.indexes AS I
    ON	(
    		DDIPS.object_id = I.object_id AND
    		DDIPS.index_id = I.index_id
    	)
    ORDER BY
           I.index_id,
           DDIPS.index_level DESC;


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Tuesday, January 20, 2015 4:19 AM
  • If the data file doesn't have enough contiguous free space within it (as the script rebuilds the index when the frag level is above 30%), the logical fragmentation won't be completely eliminated.

    Satish Kartan www.sqlfood.com

    Tuesday, January 20, 2015 5:53 AM
  • Rebuild the index with online = OFF / ON again and check the SQL Log file for any information.

    http://msdn.microsoft.com/en-us/library/ms189858.aspx

    Check data and log file size .

    Regards,

    Sajith


    http://sqllive.wordpress.com/

    Tuesday, January 20, 2015 7:51 AM
  • Thanks for replying my question. Here is the result with very high fragmented Index table:

    PK_zfAuditTStuClass 1 1 3 2 1 1 578 1 1 92.80949
    PK_zfAuditTStuClass 1 1 3 1 462 578 197354 1.251082 1.251082 54.81539
    PK_zfAuditTStuClass 1 1 3 0 4504 197354 34734021 43.8175 43.8175 99.9992
    IX_zfAuditTStuClass 0 0 4 3 1 1 5 1 1 1.519644
    IX_zfAuditTStuClass 0 0 4 2 5 5 852 1 1 52.60687
    IX_zfAuditTStuClass 0 0 4 1 847 852 166236 1.005903 1.005903 60.23989
    IX_zfAuditTStuClass 0 0 4 0 163026 166236 34734021 1.01969 1.01969 51.60466

    Tuesday, January 20, 2015 11:04 PM
  • If the data file doesn't have enough contiguous free space within it (as the script rebuilds the index when the frag level is above 30%), the logical fragmentation won't be completely eliminated.

    Satish Kartan www.sqlfood.com

    Can you manually extend your data file by a significant amount and then check if the next run of the re-index job reduces the fragmentation?

    Satish Kartan www.sqlfood.com

    Wednesday, January 21, 2015 4:48 AM
  • I doubt that this suggestion will acceptable for you.

    For very large index that won't defragment, the BEST solution is to pre-size a new database and move all your objects to that DB, then recreate your indexes there.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 21, 2015 6:40 AM
    Answerer
  • Satish,

    I changed the initial size of the data file to the larger size which was larger than the datafile size, and run the defrag again but the defragmentation percent is the same.

    Wednesday, January 21, 2015 9:38 PM
  • Uri,

    To pre-size a new db means that to set the initial size of a new db larger than the current size of the data file, right ?

    Do we have to recreate all indexes in the new pre-size db ?

    Wednesday, January 21, 2015 9:51 PM
  • Results with the headers

    name is_primary_key is_unique index_depth index_level fragment_count page_count record_count avg_fragment_size_in_pages avg_fragment_size_in_pages avg_page_space_used_in_percent
    PK_zfAuditTStudentClass 1 1 3 2 1 1 578 1 1 92.80948851
    PK_zfAuditTStudentClass 1 1 3 1 462 578 197354 1.251082251 1.251082251 54.81539412
    PK_zfAuditTStudentClass 1 1 3 0 4504 197354 34734021 43.81749556 43.81749556 99.99919694
    IX_zfAuditTStudentClass 0 0 4 3 1 1 5 1 1 1.519644181
    IX_zfAuditTStudentClass 0 0 4 2 5 5 852 1 1 52.60686929
    IX_zfAuditTStudentClass 0 0 4 1 847 852 166236 1.005903188 1.005903188 60.23989375
    IX_zfAuditTStudentClass 0 0 4 0 163026 166236 34734021 1.019690111 1.019690111 51.60465777

    Wednesday, January 21, 2015 9:55 PM
  • The issue might be that the indexes are too small or not fragmented enough.

    Is a best practise not reorganize or rebuild indexes with less than 1000 pages and also never reorganize or rebuild indexes with a  fragmentation of less than 5 percent.

    Best Regards

    P.Ceglie

    Thursday, January 22, 2015 2:03 PM
  • Hi NuocNho,

    According to your description, you want to know why fragmentation almost reaches back to same level after the index rebuilding. This issue could happen due to the huge DML operations after rebuilding index, or the inappropriate method of rebuilding index, such as online index rebuild.
     
    To troubleshoot your issue, I recommend you to use one or more of the methods below
     
    1.Use offline index rebuild if you can.
     
    2. Set 'max degree of parallelism'  to 1. For how to configure 'max degree of parallelism', please refer to the article: https://msdn.microsoft.com/en-us/library/ms189094.aspx#TsqlProcedure
     
    3.Redefine some of the clustered indexes.

    Regards,
    Michelle Li

    Thursday, January 22, 2015 2:12 PM
    Moderator
  •  inappropriate method of rebuilding index, such as online index rebuild.

    Hi Michelle Li,

    I am aware that Online Index rebuild may cause data file to grow massively but I am not aware whether it can really cause fragmentation. Can you show me some article which states that. Its just a question from my side out of curiosity


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Saturday, January 24, 2015 2:13 PM
    Moderator