SQL Datenbank Performance Optimierungen

Bei älteren OCT Versionen vor 5.10 und längerfristigem Betrieb von OCT kann es zu Performanceproblemen der SQL Server Datenbank kommen. Die folgenden Abfragen sollen eine Hilfestellung zur Erkennung und Verbesserung der Ursachen geben.

Alle Tabellen mit verwendetem Speicherplatz auflisten

SQL

SELECT 
    s.Name AS "Schema",
    t.Name AS Tabelle,
    p.Rows AS Zeilen,
    SUM(a.total_pages) * 8 / 1024 AS SpeicherplatzMB,
    SUM(a.used_pages) * 8 / 1024 AS GenutzterSpeicherplatzMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UngenutzterSpeicherplatzMB
FROM sys.tables t
INNER JOIN sys.indexes          AS i
ON t.object_id = i.object_id
INNER JOIN sys.partitions       AS p
ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a
ON p.partition_id = a.container_id
INNER JOIN sys.schemas          AS s
ON t.schema_id = s.schema_id
WHERE i.index_id <= 1
GROUP BY t.Name, s.Name, p.Rows
ORDER BY SpeicherplatzMB DESC;

Diese Abfrage liefert eine Liste von allen Tabellen mit folgenden Informationen:

Anzahl an Zeilen
gesamt belegter Speicherplatz in MB
genutzter Speicherplatz in MB
ungenutzter Speicherplatz in MB

Folgende Umstände könnten ein Indikator für Performanceprobleme sein:

ungewöhnlich hohe Werte in der Spalte “UngenutzterSpeicherplatzMB”
verhältnismäßig hoher Wert bei “SpeicherplatzMB” für die Anzahl der Zeilen

Nicht mehr benötigte Daten löschen

SQL

-- API Log bereinigen
IF OBJECT_ID('planning.tAPI_Log') IS NOT NULL
  TRUNCATE TABLE planning.tAPI_Log
IF OBJECT_ID('system.tAPI_Log') IS NOT NULL
  TRUNCATE TABLE system.tAPI_Log

-- Pipeline Log bereinigen
DELETE FROM system.tPipelineStepLog WHERE YEAR(StartTime) < YEAR(GETDATE())
DELETE From system.tPipelineLog WHERE YEAR(StartTime) < YEAR(GETDATE())
DELETE FROM system.tProcessLogs WHERE YEAR(Timestamp) < YEAR(GETDATE())

Diese SQL Befehlen löschen alle Daten aus dem API Log und alle Pipeline Log Einträge aus vorherigen Jahren. Bei längerfristigem Betrieb von OCT und vielen Pipeline-Ausführungen sind die angesprochenen Tabellen üblicherweise sehr groß und belegen viel Speicherplatz.

Überflüssige Indizes entfernen

SQL

DROP INDEX IF EXISTS icc_global_tFIN_Accounts ON global.tFIN_Accounts
DROP INDEX IF EXISTS icc_global_tFIN_CostObjects ON global.tFIN_CostObjects

In älteren Versionen wurde auf diversen global-Tabellen ein unnötiger Index erstellt, der gelöscht werden kann.

Tabellen ohne gruppierten Index

SQL

SELECT 
    s.Name as "Schema",
    t.Name AS Tabelle,
    i.Name AS IndexName,
    i.type_desc AS IndexTyp
FROM sys.tables  AS t
JOIN sys.indexes AS i
ON t.object_id = i.object_id
JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
WHERE EXISTS(
    SELECT 1
    FROM sys.tables  AS t2
    JOIN sys.indexes AS i2
    ON t.object_id = i2.object_id
    WHERE
        i2.type_desc = 'HEAP'
        and t2.object_id = t.object_id
)
AND i.Name IS NOT NULL
ORDER BY "Schema", Tabelle

Diese Abfrage liefert eine Liste von allen Tabellen, die keinen gruppierten Index haben. Üblicherweise sind die auffälligen Tabellen aus der ersten Abfrage auch in dieser Liste zu finden.

Ein gruppierter Index sorgt für eine organisierte physische Speicherung der Daten auf der Festplatte. Vor allem bei größeren Tabellen kann ein fehlender gruppierter Index zu großen Performanceproblemen beim Lesen & Schreiben führen. Dementsprechend sollte jede Tabelle einen gruppierten Index haben.

Fehlende gruppierte Indizes erstellen

SQL

DECLARE @SchemaName NVARCHAR(255)
DECLARE @TableName NVARCHAR(255)
DECLARE @IndexName NVARCHAR(255)
DECLARE @SQL NVARCHAR(MAX)

DECLARE cur CURSOR FOR
SELECT
    s.Name,
    t.Name,
    i.Name
FROM sys.tables  AS t
JOIN sys.indexes AS i
ON t.object_id = i.object_id
JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
WHERE EXISTS (
    SELECT 1
    FROM sys.tables AS t2
    JOIN sys.indexes AS i2
    ON t.object_id = i2.object_id
    WHERE
        i2.type_desc = 'HEAP'
        AND t2.object_id = t.object_id
)
AND i.type_desc = 'NONCLUSTERED'
AND i.is_primary_key = 1

OPEN cur

FETCH NEXT FROM cur
INTO @SchemaName, @TableName, @IndexName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 
        'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ' +
        'DROP CONSTRAINT [' + @IndexName + ']; ' +
        'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ' +
        'ADD CONSTRAINT [' + @IndexName + '] PRIMARY KEY CLUSTERED (RowKey ASC);'

    PRINT @SQL
    EXEC sp_executesql @SQL

    FETCH NEXT FROM cur INTO @SchemaName, @TableName, @IndexName
END

CLOSE cur
DEALLOCATE cur

Diese SQL Befehle erstellen bei allen Tabellen, die keinen gruppierten Index haben, einen gruppierten Index auf die Spalte “RowKey”, die in der Tabelle vorhanden sein muss.

Fazit

Wenn alle Skripte ausgeführt wurden, sollte sich die Performance z.B. von Pipeline-Ausführungen verbessern und der belegte Speicherplatz der Datenbank sollte sich verringern. Als mögliche Folgemaßnahme kann die Datenbank-Datei verkleinert werden, um zusätzlichen Speicherplatz auf der Festplatte freizugeben.