====== 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.
====== Externe Optimierung =====
Darüber hinaus können die folgenden bat-Scripte verwendet werden, um die Perofrmance der Datenbank zu verbessern:
==== Update der Tabellen-Statistiken ====
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 [-E | -U -P ]
:: ============================================================
SETLOCAL
SET SERVER=%~2
IF "%SERVER%"=="" (
ECHO Verwendung: %~nx0 -S ^ [-E ^| -U ^ -P ^]
EXIT /B 1
)
:: Auth-Parameter = alles nach -S
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%
==== Performance-Überwachungs-Tool Query Store aktivieren ====
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 [-E | -U -P ]
:: ============================================================
SETLOCAL
SET SERVER=%~2
IF "%SERVER%"=="" (
ECHO Verwendung: %~nx0 -S ^ [-E ^| -U ^ -P ^]
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%
==== Defragmentierung der Datenbank ====
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 [-E | -U -P ]
:: ============================================================
SETLOCAL
SET SERVER=%~2
IF "%SERVER%"=="" (
ECHO Verwendung: %~nx0 -S ^ [-E ^| -U ^ -P ^]
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%