none
Trying to understand index rebuid/reorganize

    Question

  • Hello all,

    I'm trying to understand the index rebuild/reorganize options in SQL server.

    I'm nubby in SQL server and coming from SharePoint world.

    Trying to implement the automation script of Ola Halengren for "SQL Server Index and Statistics Maintenace", but first of all I would like to understand what all of these scripts are doing. So I downloaded the complete script and ran it on my test environment. It created several SQL jobs (several for backup of the DB's), but I have a very good backup strategy and I want to continue using it.

    So as a first question: Could anybody guide me which SQL scripts I need to download  in order to implement ONLY index rebuild/reorganize functionality, if of course this is possible. The link to the scripts is here . And where should this stored procedures be created (e.g. under master DB, under all content databases or  somewhere else)? Based on what I experienced from my test environment I've to create a custom SQL job after the store procedures are create in order to schedule index rebuild/reorganize.

    The second question is following: I ran the following SQL script against one of the content databases:

    To get the table name, index name and average index fragmentation in %

    As you could see from the build DocStreams table is highly fragmented, isn't it?

    So I expand the DocStream table and tried to reorganize all indexes and get the following picture:

    As you could see the Total Fragmentation is almost 0. I don't understand why in the report it is displayed as 100% fragmented and when I try to reorganize all indexes I get 0 fragmentation. Could someone explain me: Am I doing something wrong?

    Thank you in advance for all reply's.


    Please remember to mark the replies as an answers if they help and unmark them if they provide no help.

    Friday, April 21, 2017 1:57 PM

Answers

  • Finally get some understanding about the "solution" from Ola Halengren.

    You can create the objects (store procedures) in master db, as he suggested. You need to understand the dependencies of different scripts. Simply go to the store procedure -> right click -> View Dependecies:

    You will be able to see what else is needed:

    As in my case if you need only index rebuild/reorganization you will need the 3 sql scripts shown above.

    When you execute them and objects are created you need to create a custom SQL job and schedule it according the recommendations given by the guy, for Index rebuild/reorganize once per week and before a full backup execution.

    The needs to run the following sqlcmd command: sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @LogToTable = 'Y'" -b

    I strongly encourage you to read the FAQ section of his site. There you will get answers of more of the questions.

    With this post I think the topic at least for me could be handled as closed.


    Please remember to mark the replies as an answers if they help and unmark them if they provide no help.


    Thursday, April 27, 2017 9:54 AM

All replies

  • You only need the IndexOptimize script if you want to just implement Ola's index maintenance solution.

    Here's the details on Index maintenance solution:
    Index and Statistics Maintenance

    You can create the IndexOptimize stored procedure in master database or any user database of your choice (only one database).

    I think the apparent discrepancy you are seeing is actually that one is showing 'Total Fragmentation', whereas the fragmentation script you are running shows '_average_ index fragmentation'. Different things.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Friday, April 21, 2017 3:29 PM
    Friday, April 21, 2017 3:25 PM
  • Hello,

    There is a generally accepted solution based on the percent of fragmentation.

    • Fragmentation is between 10-30% – it is suggested to perform index reorganization
    • Fragmentation is higher than 30% – it is suggested to perform index rebuild


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Sunday, April 23, 2017 7:36 AM
  • Hello philfactor and Vishe,

    thank you for your reply's but that is not what I expected. Sorry.

    1. IndexOptimize.sql script is dependent from CommandExecute.sql and CommandLog.sql, tested.
    2. I know that based on the index fragmentation either index rebuild or index reorganization is needed, the decision will be made by the script when it runs.

    Thanks for reply's guys.

    Anyone else with any other suggestions/comments?


    Please remember to mark the replies as an answers if they help and unmark them if they provide no help.

    Sunday, April 23, 2017 11:17 AM
  • Finally get some understanding about the "solution" from Ola Halengren.

    You can create the objects (store procedures) in master db, as he suggested. You need to understand the dependencies of different scripts. Simply go to the store procedure -> right click -> View Dependecies:

    You will be able to see what else is needed:

    As in my case if you need only index rebuild/reorganization you will need the 3 sql scripts shown above.

    When you execute them and objects are created you need to create a custom SQL job and schedule it according the recommendations given by the guy, for Index rebuild/reorganize once per week and before a full backup execution.

    The needs to run the following sqlcmd command: sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @LogToTable = 'Y'" -b

    I strongly encourage you to read the FAQ section of his site. There you will get answers of more of the questions.

    With this post I think the topic at least for me could be handled as closed.


    Please remember to mark the replies as an answers if they help and unmark them if they provide no help.


    Thursday, April 27, 2017 9:54 AM