I stumbled across a stored procedure and wanted to clarify a few things first as I’ve been told it needs “rigorous review before being executed on any server”, and “testing before we run it on a production server”.

We need to know and understand what the SP does at each stage to ensure no security risks are being introduced and that there is no potential for a data breach or destabilisation of the environment.

Below is our current SP which runs on a Sunday night but within the day the gains are lost and fragmentation is up to 99% on a large number of tables

enter image description here

Additional NONE of our tables have a fill factor at all and all set to zero (equivalent to 100), wondered if this matters

enter image description here

Forgive the email I literally am very new to the Developer world with no prior experience of any DBA related issues.

Our current rebuild / reorganise code :

/****** Object:  StoredProcedure [dbo].[IndexMaintenance_sp]    Script Date: 25/05/2018 13:05:28 ******/


CREATE PROCEDURE [dbo].[IndexMaintenance_sp] @log_only BIT = 1
    DECLARE @tableName VARCHAR(800)
        ,@indexName VARCHAR(800)
        ,@fragmentation FLOAT
        ,@indexId INT = 0
        ,@prevTempIndexId INT = 0
        ,@pageCount INT = 0
        ,@tableId INT
        ,@tempIndexId INT = 0;

    CREATE TABLE #tempIndexes (
        id INT IDENTITY
        ,objectId INT
        ,name VARCHAR(max)
        ,indexId INT

    INSERT INTO #tempIndexes (
    SELECT i.object_id
        ,i.name AS Name
    FROM [sys].indexes AS i
    WHERE i.name IS NOT NULL
        AND i.object_id IS NOT NULL;

    WHILE 1 = 1
        SELECT @tempIndexId = id
            ,@indexId = indexId
            ,@tableId = objectId
        FROM #tempIndexes
        WHERE id = (
                SELECT min(id)
                FROM #tempIndexes
                WHERE id > @prevTempIndexId

        IF @tempIndexId = @prevTempIndexId

        SET @prevTempIndexId = @tempIndexId;
        SET @indexName = NULL;

        SELECT TOP 1 @tableId = s.object_id
            ,@tableName = OBJECT_NAME(s.object_id)
            ,@indexName = i.name
            ,/*s.index_id,*/ @pageCount = s.page_count /*, s.index_type_desc*/
            ,@fragmentation = s.avg_fragmentation_in_percent /*, s.fragment_count */
        FROM [sys].[dm_db_index_physical_stats](DB_ID(), @tableId, @indexId, NULL, NULL) AS s
        INNER JOIN [sys].indexes AS i ON i.object_id = s.object_id
            AND i.index_id = s.index_id
        WHERE avg_fragmentation_in_percent > 5.0
            AND s.object_id IS NOT NULL
            AND i.name IS NOT NULL;

        IF @indexName IS NULL

        INSERT INTO [ServerHealth].[dbo].[__tblIndexFragmentation] (
        VALUES (

        IF @pageCount < 1000

        DECLARE @sql NVARCHAR(2000);

        SET @sql = 'ALTER INDEX [' + @indexName + '] ON [dbo].[' + @tableName;

        IF @fragmentation < 30
            SET @sql = @sql + '] REORGANIZE;';
            SET @sql = @sql + '] REBUILD;';

        IF @log_only = 0
            EXEC (@sql);
  • Its not clear what you are asking with your "current rebuild code" May 29, 2018 at 10:30
  • 1
    Some fragmentation is normal. The question is whether or not the level you have is causing you problems. May 29, 2018 at 10:46
  • I'd consider adding a check on what the fragmentation levels are after your procedure is run. You are setting @log_only to 0 when trying to actually run this in production, correct? By default, that's set to 1, which means the rebuild/reorg commands will not be run.
    – RDFozz
    May 29, 2018 at 19:19
  • FYI, I voted-to-close this as "primarily opinion based". Only you can say if Ola's code does what you want.
    – Hannah Vernon
    May 30, 2018 at 20:06

2 Answers 2


To answer your question, yes Ola Hallengrens scripts are highly respected and safe to use. As much as any 3rd party tools can be. How you choose to use (or mis-use) them are up to you. Make sure you have the current version.

The best advice i could give you if you unsure is to test them yourself somewhere safe and form your own opinion.

Take the time to read the guide and understand the default settings ( They are a good starting point) and then test them yourself.

Although Olas scripts are awesome. They're not a silver bullet. If you have a table hitting 99% fragmentation in one day you have something else going on. Are you shrinking your db daily?


Firstly as mentioned the Ola scripts are safe and very thoroughly tested across a lot of people.

Your fill factor should only really ever be set to 100% (0) if the index will only ever add onto the end and delete from the beginning (aka a bigint identity field is fine)

If you have data that will insert into the middle of data sets so in order of a person then when you're adding to that index you will want to insert into the middle of your dataset. if the fill factor is 100 then there is no space and it will create / add to a new record completely out of place fragmenting the index.

There's no 100% definitive answer to what is the right fill factor to use, and it can change for different indexes on the same tables. if something is constantly fragmenting then lowering the fill factor can help alleviate that.

NOTE reducing fill factor while can reduce fragmentation, WILL increase disk space usage. if you have an index that is FF100 and uses 100GB and you change it to FF50 then it will now be using 200GB of disk space for the same amount of data.

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