Dies ist der technische Teil dieses Blog-Eintrags: Technischer Blog it at Service

Dynamischer Script zum setzen des Fillfactors. Dieser Script ermittelt den Fillfactor und erzeugt SQL Kommandos zur Rekonfiguration des jeweiligen Index. Alle ausgeführten Kommandos werden in der Command-Log Table von Ola Hallengreen (SQL Server Maintenance Solution) dokumentiert:

USE 'Datenbankname';

-- Dynamisches Skript zur Anpassung des Fill Factors und Logging
SELECT
    cl.IndexName,
    i.fill_factor AS CurrentFillFactor,
    COUNT(*) AS IndexOccurrenceCount,
    CASE
        WHEN i.fill_factor > 75 THEN
            'SET @StartTime = GETDATE(); ' +
            'BEGIN TRY ' +
            '    SET @SQL = N''ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' REBUILD WITH (FILLFACTOR = ' + CAST((i.fill_factor - 5) AS NVARCHAR) + ', SORT_IN_TEMPDB = OFF, ONLINE = OFF);''; ' +
            '    PRINT @SQL; ' +  -- Optional zur Überprüfung
            '    EXEC sp_executesql @SQL; ' +
            '    INSERT INTO master.dbo.CommandLog (DatabaseName, SchemaName, ObjectName, ObjectType, IndexName, Command, CommandType, StartTime, EndTime, ErrorNumber, ErrorMessage) ' +
            '    VALUES (DB_NAME(), ''' + s.name + ''', ''' + o.name + ''', ''IN'', ''' + i.name + ''', @SQL, ''REBUILD'', @StartTime, GETDATE(), 0, NULL); ' +
            'END TRY ' +
            'BEGIN CATCH ' +
            '    INSERT INTO master.dbo.CommandLog (DatabaseName, SchemaName, ObjectName, ObjectType, IndexName, Command, CommandType, StartTime, EndTime, ErrorNumber, ErrorMessage) ' +
            '    VALUES (DB_NAME(), ''' + s.name + ''', ''' + o.name + ''', ''IN'', ''' + i.name + ''', @SQL, ''REBUILD'', @StartTime, GETDATE(), ERROR_NUMBER(), ERROR_MESSAGE()); ' +
            'END CATCH;'
        ELSE
            'No change required'
    END AS RebuildScript
FROM
    master.dbo.CommandLog AS cl
JOIN
    sys.indexes AS i
    ON cl.IndexName COLLATE Latin1_General_CS_AS_KS_WS = i.name COLLATE Latin1_General_CS_AS_KS_WS
JOIN
    sys.objects AS o
    ON i.object_id = o.object_id
JOIN
    sys.schemas AS s
    ON o.schema_id = s.schema_id
WHERE
    cl.StartTime >= '2024-07-22 00:00:00'
    AND cl.EndTime <= '2024-10-05 23:59:59'
    AND cl.CommandType IN ('REORGANIZE', 'REBUILD')
GROUP BY
    cl.IndexName,
    i.fill_factor,
    i.name,
    s.name,
    o.name
ORDER BY
    IndexOccurrenceCount DESC;