Is Ola Hallengrens Stored Procedure what we need and safe to use [closed]

Question

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

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

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 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[IndexMaintenance_sp] @log_only BIT = 1
AS
BEGIN
    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 (
        objectId
        ,name
        ,indexId
        )
    SELECT i.object_id
        ,i.name AS Name
        ,i.index_id
    FROM [sys].indexes AS i
    WHERE i.name IS NOT NULL
        AND i.object_id IS NOT NULL;

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

        IF @tempIndexId = @prevTempIndexId
            BREAK;

        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
            CONTINUE;

        INSERT INTO [ServerHealth].[dbo].[__tblIndexFragmentation] (
            checkDate
            ,databaseId
            ,databaseName
            ,objectId
            ,objectName
            ,indexid
            ,indexName
            ,indexPages
            ,indexFragmentation
            )
        VALUES (
            GetDate()
            ,DB_ID()
            ,DB_NAME(DB_ID())
            ,@tableId
            ,@tableName
            ,@indexId
            ,@indexName
            ,@pageCount
            ,@fragmentation
            );

        IF @pageCount < 1000
            CONTINUE;

        DECLARE @sql NVARCHAR(2000);

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

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

        IF @log_only = 0
        BEGIN
            EXEC (@sql);
        END
    END
END
GO

Answer 1

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?

Answer 2

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.