Kapitel 4: Datenbankoptimierung

Sollte es im laufenden Betrieb zu Leistungsproblemen kommen, können Sie die Datenbank über die Schaltfläche „Datenbank optimieren“ unter der Maske “Wartung” automatisch optimieren.

Darüber hinaus können die folgenden bat-Scripte verwendet werden, um die Perofrmance der Datenbank zu verbessern:

Der SQL Server verwendet Statistiken über die Verteilung von Daten in den Tabellen, um zu entscheiden, wie er Abfragen am schnellsten verarbeitet (welche Indizes genutzt werden, wie Tabellen verknüpft werden usw.).

Wenn viele Daten hinzugefügt, geändert oder gelöscht werden, veralten diese Statistiken. Ohne Aktualisierung wählt der Datenbank-Server falsche Ausführungspläne, was das Gesamtsystem enorm verlangsamen kann.

Wann das sinnvoll ist:

Regelmäßige Wartung (z. B. wöchentlich/nächtlich): Außerhalb der Arbeitszeiten per Windows-Aufgabenplanung (Task Scheduler), um die Performance stabil zu halten.

Nach großen Datenimporten/Löschaktionen: Wenn auf einen Schlag Tausende Datensätze hinzugefügt oder geändert wurden.

Bei Performance-Problemen: Wenn die Anwendung plötzlich ungewöhnlich langsam reagiert, obwohl die Serverauslastung normal wirkt.

Dies können sie mit dem folgenden bat-Script automatisieren:

@ECHO OFF
:: ============================================================
:: update-statistics.bat -S <Server> [-E | -U <User> -P <Pass>]
:: ============================================================
SETLOCAL

SET SERVER=%~2
IF "%SERVER%"=="" (
    ECHO Verwendung: %~nx0 -S ^<Server^> [-E ^| -U ^<User^> -P ^<Pass^>]
    EXIT /B 1
)

:: Auth-Parameter = alles nach -S <Server>
SET AUTH=%3 %4 %5 %6 %7
IF "%AUTH: =%"=="" SET AUTH=-E

SET LOG=%~dp0update-statistics_%DATE:/=-%.log

ECHO [%DATE% %TIME%] Start: %SERVER% >> "%LOG%"

sqlcmd -S "%SERVER%" %AUTH% -d "VISTRAX_3_6_MANDATOR_1" -b ^
    -Q "EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN';" >> "%LOG%" 2>&1
IF %ERRORLEVEL% NEQ 0 ECHO [FEHLER] VISTRAX_3_6_MANDATOR_1 & GOTO :END

sqlcmd -S "%SERVER%" %AUTH% -d "VISTRAX_3_6" -b ^
    -Q "EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN';" >> "%LOG%" 2>&1
IF %ERRORLEVEL% NEQ 0 ECHO [FEHLER] VISTRAX_3_6 & GOTO :END

ECHO [%DATE% %TIME%] Erfolgreich abgeschlossen. >> "%LOG%"
ECHO Erfolgreich. Log: %LOG%

:END
ENDLOCAL
EXIT /B %ERRORLEVEL%

Der Query Store (Abfragespeicher) ist eine eingebaute Funktion von Microsoft SQL Server, die wie ein “Flugschreiber” für Datenbanken funktioniert. Er zeichnet automatisch auf, welche SQL-Abfragen ausgeführt werden, wie lange sie dauern und welche Ressourcen sie verbrauchen.

Wann das sinnvoll ist:

Performance-Analyse: Wenn Datenbankabfragen langsam laufen und du herausfinden willst, welche Abfragen die meisten Ressourcen (CPU, RAM, I/O) verbrauchen.

Fehlersuche bei Updates: Um Performance-Einbrüche (“Parameter Sniffing” oder schlechte Ausführungspläne) nach Software-Updates oder Datenbank-Migrationen zu erkennen und alte Ausführungspläne zu erzwingen.

sie können dies über das folgende .bat.Script aktivieren:

@ECHO OFF
:: ============================================================
:: enable-querystore-with-cleanup.bat -S <Server> [-E | -U <User> -P <Pass>]
:: ============================================================
SETLOCAL

SET SERVER=%~2
IF "%SERVER%"=="" (
    ECHO Verwendung: %~nx0 -S ^<Server^> [-E ^| -U ^<User^> -P ^<Pass^>]
    EXIT /B 1
)

SET AUTH=%3 %4 %5 %6 %7
IF "%AUTH: =%"=="" SET AUTH=-E

SET SQL=^
ALTER DATABASE VISTRAX_3_6 SET QUERY_STORE = ON;^
ALTER DATABASE VISTRAX_3_6 SET QUERY_STORE (OPERATION_MODE = READ_WRITE, MAX_STORAGE_SIZE_MB = 100, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));^
ALTER DATABASE VISTRAX_3_6_MANDATOR_1 SET QUERY_STORE = ON;^
ALTER DATABASE VISTRAX_3_6_MANDATOR_1 SET QUERY_STORE (OPERATION_MODE = READ_WRITE, MAX_STORAGE_SIZE_MB = 100, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));

sqlcmd -S "%SERVER%" %AUTH% -b -Q "%SQL%"

IF %ERRORLEVEL% EQU 0 (
    ECHO Query Store erfolgreich aktiviert.
) ELSE (
    ECHO [FEHLER] Query Store konnte nicht aktiviert werden.
)

ENDLOCAL
EXIT /B %ERRORLEVEL%

Zunächst muss die folgende SQL Query ausgeführt werden:

-------------------------------------------------------------------------------
-- Dieser Query stellt die Fragementierung der Indices in beiden Tabellen fest
-- und defragmentiert diese bei Bedarf.
-------------------------------------------------------------------------------
-- Fragmentierung   Aktion          Grund
-- 10–30%           REORGANIZE      Online, keine Sperrung, leicht
-- > 30%            REBUILD         Vollständige Neustrukturierung
-- < 10%            (übersprungen)  Kein Handlungsbedarf
-------------------------------------------------------------------------------


-- ============================================
-- Alle Indizes defragmentieren – VISTRAX_3_6
-- ============================================
USE VISTRAX_3_6;
GO

DECLARE @TableName    NVARCHAR(256);
DECLARE @IndexName    NVARCHAR(128);
DECLARE @Fragmentation DECIMAL(5,2);
DECLARE @SQL          NVARCHAR(512);

DECLARE frag_cursor CURSOR FOR
    SELECT
        OBJECT_SCHEMA_NAME(ips.object_id) + '.' + OBJECT_NAME(ips.object_id),
        i.name,
        ips.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
    JOIN sys.indexes i
        ON ips.object_id = i.object_id AND ips.index_id = i.index_id
    WHERE ips.index_id > 0          -- Keine Heaps
        AND ips.page_count >= 10    -- Nur relevante Indizes
        AND ips.avg_fragmentation_in_percent >= 10.0
    ORDER BY ips.avg_fragmentation_in_percent DESC;

OPEN frag_cursor;
FETCH NEXT FROM frag_cursor INTO @TableName, @IndexName, @Fragmentation;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Fragmentation >= 30.0
    BEGIN
        SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON ' + @TableName + ' REBUILD;';
        PRINT 'REBUILD  (' + CAST(@Fragmentation AS VARCHAR(5)) + '%)  -> ' + @TableName + '.' + @IndexName;
    END
    ELSE
    BEGIN
        SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON ' + @TableName + ' REORGANIZE;';
        PRINT 'REORGANIZE  (' + CAST(@Fragmentation AS VARCHAR(5)) + '%) ->' + @TableName + '.' + @IndexName;
    END

    EXEC sp_executesql @SQL;

    FETCH NEXT FROM frag_cursor INTO @TableName, @IndexName, @Fragmentation;
END;

CLOSE frag_cursor;
DEALLOCATE frag_cursor;

PRINT 'VISTRAX_3_6 – Defragmentierung abgeschlossen.';
GO

-- ===================================================
-- Alle Indizes defragmentieren – VISTRAX_3_6_MANDATOR_1
-- ===================================================
USE VISTRAX_3_6_MANDATOR_1;
GO

DECLARE @TableName    NVARCHAR(256);
DECLARE @IndexName    NVARCHAR(128);
DECLARE @Fragmentation DECIMAL(5,2);
DECLARE @SQL          NVARCHAR(512);

DECLARE frag_cursor CURSOR FOR
    SELECT
        OBJECT_SCHEMA_NAME(ips.object_id) + '.' + OBJECT_NAME(ips.object_id),
        i.name,
        ips.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
    JOIN sys.indexes i
        ON ips.object_id = i.object_id AND ips.index_id = i.index_id
    WHERE ips.index_id > 0
        AND ips.page_count >= 10
        AND ips.avg_fragmentation_in_percent >= 10.0
    ORDER BY ips.avg_fragmentation_in_percent DESC;

OPEN frag_cursor;
FETCH NEXT FROM frag_cursor INTO @TableName, @IndexName, @Fragmentation;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Fragmentation >= 30.0
    BEGIN
        SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON ' + @TableName + ' REBUILD;';
        PRINT 'REBUILD  (' + CAST(@Fragmentation AS VARCHAR(5)) + '%)  -> ' + @TableName + '.' + @IndexName;
    END
    ELSE
    BEGIN
        SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON ' + @TableName + ' REORGANIZE;';
        PRINT 'REORGANIZE  (' + CAST(@Fragmentation AS VARCHAR(5)) + '%)  -> ' + @TableName + '.' + @IndexName;
    END

    EXEC sp_executesql @SQL;

    FETCH NEXT FROM frag_cursor INTO @TableName, @IndexName, @Fragmentation;
END;

CLOSE frag_cursor;
DEALLOCATE frag_cursor;

PRINT 'VISTRAX_3_6_MANDATOR_1 – Defragmentierung abgeschlossen.';
GO

Anschließend kann das zugehörie Bat Script verwendet werden, um die Fragmeniterung der Bat Script um Fragementierung zu prüfen und ggf. zu defragmentieren:

@ECHO OFF
:: ============================================================
:: defrag-indices-on-necessity.bat -S <Server> [-E | -U <User> -P <Pass>]
:: ============================================================
SETLOCAL

SET SERVER=%~2
IF "%SERVER%"=="" (
    ECHO Verwendung: %~nx0 -S ^<Server^> [-E ^| -U ^<User^> -P ^<Pass^>]
    EXIT /B 1
)

SET AUTH=%3 %4 %5 %6 %7
IF "%AUTH: =%"=="" SET AUTH=-E

SET SQLFILE=%~dp0check-and-defrag-Indices-both-databases.sql

IF NOT EXIST "%SQLFILE%" (
    ECHO [FEHLER] SQL-Datei nicht gefunden: %SQLFILE%
    EXIT /B 1
)

sqlcmd -S "%SERVER%" %AUTH% -b -i "%SQLFILE%"

IF %ERRORLEVEL% EQU 0 (
    ECHO Index-Defragmentierung erfolgreich abgeschlossen.
) ELSE (
    ECHO [FEHLER] Defragmentierung fehlgeschlagen.
)

ENDLOCAL
EXIT /B %ERRORLEVEL%

Kapitel 4: Datenbankoptimierung

Externe Optimierung

Update der Tabellen-Statistiken

Performance-Überwachungs-Tool Query Store aktivieren

Defragmentierung der Datenbank