locked
Rebuild indexes and Reorganizing indexes RRS feed

  • Question

  • Hello,

    I have an issue with fragmentation.I created maintenance plan and schedule rebuild and reorganizing indexes.Every time executed successfully.but still i am facing fragmentation issues.

    manuall i ran below query 

    SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
    avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
    FROM sys.dm_db_index_physical_stats
    (DB_ID(N''), NULL, NULL, NULL , 'LIMITED')
    ORDER BY avg_fragmentation_in_percent DESC

    and i ran the below code.the below shows high fragementation  100

    SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
    avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
    FROM sys.dm_db_index_physical_stats
    (DB_ID(N'TCSUUSPROD'), NULL, NULL, NULL , 'DETAILED')
    ORDER BY avg_fragmentation_in_percent DESC

    Please ans the following quetions?

    pls provide solution for reducing high fragmentation issue.and what is the best level of fill factor value?

    please suggest how to check different ways for  reducing high fragmenation issue?

    please suggest rebuild and reorganize indexes through maintenance plan is correct or not?

    anyone please provide script for rebuild and reorganize indexes through jobs.

    why high fragmenation after rebuild indexes?

    thank you


    • Edited by RAJ927 Thursday, August 11, 2016 6:30 AM
    Thursday, August 11, 2016 6:28 AM

Answers

  • Thank you Shashank,

    I saw your script.Can you please Tell me how to execute your script through job.Please tell me.

    Thank you

    You need to first download the script then copy the script. Then create new job and in content paste the script and appropriately configure the job. I hope you know how to put script in job and run it via agent.

    Before running it via agent first paste script in SSMS new query windows and run manually, this is just to see things are going fine and after it runs successfully configure it to run via job


    Cheers,

    Shashank

    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 Articles

    MVP

    • Proposed as answer by Teige Gao Thursday, August 18, 2016 2:35 AM
    • Marked as answer by RAJ927 Thursday, August 25, 2016 12:27 AM
    Wednesday, August 17, 2016 2:56 PM
    Answerer

All replies

  • Hi RAJ927,

    you can use freely the well-known Ola Hallengren's scripts for indexes and stats:

    https://ola.hallengren.com/

    HTH

    Thursday, August 11, 2016 11:37 AM
  • Raj I can see lot of indexes are having page count < 2000 for such small indexes we do not need to rebuild,reorganize index so your query would change like

    SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
    avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
    FROM sys.dm_db_index_physical_stats
    (DB_ID(N''), NULL, NULL, NULL , 'LIMITED')
    where page_count > 2000
    ORDER BY avg_fragmentation_in_percent DESC

    So you used two option DETAILED and LIMITED. Actually I would suggest limited if you have big databases and detailed if you have small databases because when you use detailed pages at all level of index is scanned and it can take huge time.

    In both query you posted just use filter page_count > 2000 and then rebuild index.

    I have my custom script here, its light weight script not as detailed as Ola's script but in your case would do the job


    Cheers,

    Shashank

    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 Articles

    MVP



    Thursday, August 11, 2016 12:19 PM
    Answerer
  • Thank you Shanky,

    I have one small question.After executing you provide query looks like .The column avg_fragmentation_in_percent value less than 5%.So in this case these indexes rebuild or not. please guide me.

    Thursday, August 11, 2016 1:46 PM
  • Yes you can safely ignore, none of the big indexes, which would actually matter, are fragmanted

    Cheers,

    Shashank

    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 Articles

    MVP

    • Proposed as answer by Teige Gao Friday, August 12, 2016 1:25 AM
    Thursday, August 11, 2016 1:51 PM
    Answerer
  • Thank you Shashank,

    I saw your script.Can you please Tell me how to execute your script through job.Please tell me.

    Thank you

    Wednesday, August 17, 2016 2:48 PM
  • Thank you Shashank,

    I saw your script.Can you please Tell me how to execute your script through job.Please tell me.

    Thank you

    You need to first download the script then copy the script. Then create new job and in content paste the script and appropriately configure the job. I hope you know how to put script in job and run it via agent.

    Before running it via agent first paste script in SSMS new query windows and run manually, this is just to see things are going fine and after it runs successfully configure it to run via job


    Cheers,

    Shashank

    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 Articles

    MVP

    • Proposed as answer by Teige Gao Thursday, August 18, 2016 2:35 AM
    • Marked as answer by RAJ927 Thursday, August 25, 2016 12:27 AM
    Wednesday, August 17, 2016 2:56 PM
    Answerer