Is Ola Hallengrens Stored Procedure what we need and safe to use 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 &gt; @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 &gt; 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 &lt; 1000
            CONTINUE;

        DECLARE @sql NVARCHAR(2000);

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

        IF @fragmentation &lt; 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
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?