Mit bestimmter Regelmäßigkeit werde ich beauftragt, vorhandene Microsoft SQL Server zu untersuchen, wenn zum Beispiel eine Performance-Analyse gemacht werden soll oder aber der Microsoft SQL Server einer generellen Untersuchung unterzogen werden soll. Das man dabei schon mal recht interessanteste Analysen vorfindet, habe ich bereits im Artikel “Berater / DBA / DEV – Dokumentation ist eine Hauptleistungspflicht!” behandelt. Mit diesem Artikel möchte ich eine Artikelreihe beginnen, in der ich ein paar meiner im Alltag verwendeten Skripte vorstelle und deren Interpretation beschreibe.

Wenn Datenbanken auf Performanceprobleme untersucht werden müssen, gilt der erste Blick bestimmten Konfigurationsparametern einer Datenbank. Diese Einstellungen einzeln über die GUI des Microsoft SQL Server Management Studio zu ermitteln, kann – insbesondere, wenn es sehr viele Datenbanken sind - sehr mühsam und vor allen Dingen sehr zeitraubend sein. Da die Ergebnisse in die Dokumentation einfließen müssen, ist eine tabellarische Auswertung die bessere Wahl. Aus diesem Grund helfe ich mir – und dem Kunden – mit einem Skript, dass mir in wenigen Augenblicken einen – auf die Performanceanalyse einer Datenbank ausgerichteten – Überblick verschafft.

USE master;

GO

-- Dirty reads zulassen, um möglichst keine Ressourcen zu sperren!

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

GO

-- Tabellenvariable für die Speicherung der Ergebnisse

DECLARE    @Result TABLE

(

    Database_Name         sysname        NOT NULL,

    Database_Owner        sysname        NULL,

    compatibility_level   VARCHAR(10)    NOT NULL,

    collation_Name        sysname        NOT NULL,

    snapshot_isolation    VARCHAR(5)     NOT NULL    DEFAULT ('OFF'),

    read_committed_SI     TINYINT        NOT NULL    DEFAULT (0),                

    Logical_Name          sysname        NOT NULL,

    type_desc             VARCHAR(10)    NOT NULL,

    physical_name         VARCHAR(255)   NOT NULL,

    size_MB               DECIMAL(18, 2) NOT NULL    DEFAULT (0),

    growth_MB             DECIMAL(18, 2) NOT NULL    DEFAULT (0),

    used_MB               DECIMAL(18, 2) NOT NULL    DEFAULT (0),

    is_percent_growth     TINYINT        NOT NULL    DEFAULT (0),

    PRIMARY KEY CLUSTERED

    (

        Database_Name,

        logical_name

    ),

    UNIQUE (physical_name)    

);

INSERT INTO @Result

EXEC    sys.sp_MSforeachdb @command1 = N'USE [?];

SELECT  DB_NAME(D.database_id)                            AS [Database Name],

        SP.name                                           AS [Database_Owner],

        D.compatibility_level,

        D.collation_name,

        D.snapshot_isolation_state_desc,

        D.is_read_committed_snapshot_on,

        MF.name,

        MF.type_desc,

        MF.physical_name,

        MF.size / 128.0                                   AS [size_MB],

        CASE WHEN MF.[is_percent_growth] = 1

            THEN MF.[size] * (MF.[growth] / 100.0)

            ELSE MF.[growth]

        END    / 128.0                                    AS [growth_MB],

        FILEPROPERTY(MF.name, ''spaceused'') / 128.0 AS [used_MB],

        MF.[is_percent_growth]

FROM    sys.databases AS D INNER JOIN sys.master_files AS MF

        ON    (D.database_id = MF.database_id) LEFT JOIN sys.server_principals AS SP

        ON    (D.owner_sid = SP.sid)

WHERE    D.database_id = DB_ID();';

-- Ausgabe des Ergebnisses für alle Datenbanken

SELECT * FROM @Result AS R;

-- Umstellung der Isolationsstufe

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

GO

Dieses Skript erstellt zunächst eine Tabellenvariable, um anschließend mit Hilfe der Systemprozedur sys.sp_MSforeachdb die benötigten Informationen zu jeder Datenbank zu ermitteln. Leider ist dieser Workaround notwendig, da die Funktion FILEPROPERTY nur im Kontext der AKTUELLEN Datenbank Werte liefern kann. Es gilt zu beachten, dass sys.sp_MSforeachdb eine nicht offiziell dokumentierte Systemprozedur von Microsoft ist. Das Ergebnis liefert eine Tabelle, in der für jede Datei einer Datenbank die folgenden Informationen gespeichert werden:

[Database_Name]

Hier bedarf es sicherlich keiner Erklärung; um welche Datenbank handelt es sich?

[Database_Owner]

Dieser Wert sollte aus Sicherheitsgründen sehr genau überprüft werden. Da der Schwerpunkt dieses Artikels auf Performance ausgelegt ist, möchte ich hier auf den lesenswerten Artikel “SQL Server Database Ownership: survey results & recommendations” von Andreas Wolter (b | t) verweisen, der – basierend auf einer Umfrage – Informationen und Empfehlungen zum Eigentümer einer Datenbank gibt.

[Compatibility_Level]

Diese Spalte zeigt, mit welchem Kompatibilitätsmodus die Datenbank betrieben wird. Sofern es sich bei dem zu untersuchenden Microsoft SQL Server um die Version von 7.0 – 2012 handelt, ist diese Option (immer unter Berücksichtigung des Aspekts der Performance) eher zu vernachlässigen. Kommt jedoch der Microsoft SQL Server 2014 ins Spiel, kann diese Option einen wichtigen Hinweis zur Verwendung des neuen “Cardinal Estimator” im Query Optimizer geben. Nur, wenn eine Datenbank im Modus “120” läuft, kann diese neue Möglichkeit genutzt werden.

[Collation_Name]

Die konfigurierte Sortierung für eine Datenbank kann erheblichen Einfluss auf die Performance von Abfragen haben, wenn in der Datenbank Abfragen ausgeführt werden, die datenbankübergreifend arbeiten. Zu diesem Thema habe ich im Artikel “Sortierungskonflikte – Auswirkungen auf Ausführungspläne” die Auswirkungen an Hand einer so tatsächlich vorgefundenen Problemanalyse beschrieben. Wird häufig mit temporären Tabellen gearbeitet, sollte die Sortierung der einzelnen Datenbank mit der Sortierung von TEMPDB verglichen werden.

[SNAPSHOT_ISOLATION] und [READ_COMMITTED_SNAPSHOT]

Snapshot Isolation bietet in Microsoft SQL Server die Möglichkeit, ein pessimistisches Isolationsmodell in ein – bedingt – optimistisches Transaktionsmodell zu  verwandeln. Die Prüfung auf SNAPSHOT_ISOLATION ist insofern für mögliche Performanceprobleme relevant, als das – einfach ausgedrückt – bei einem SELECT die Daten nicht unmittelbar aus der Tabelle gelesen werden, sondern einen Umweg über die TEMPDB machen. SNAPSHOT_ISOLATION beeinflusst also die Performance deutlich, da die TEMPDB bei dieser Isolationsstufe eine wichtige Rolle spielt. Die Thematik zu SNAPSHOT ISOLATION würde diesen Artikel vollständig sprengen. Ein sehr guter Einstieg in die Thematik findet sich hier: “Snapshotisolation in SQL Server”.

[LOGICAL_NAME]

Hinter [LOGICAL_NAME] verbirgt sich – wie es der Name bereits sagt – die logische Bezeichnung einer physikalischen Datei einer Datenbank. Dieser logische Name muss z. B. verwendet werden, um die Funktion FILEPROPERTY zu verwenden oder aber, um mit SHRINKFILE eine Datenbankdatei zu verkleinern. Dieses Attribut hat informellen Charakter in der Ausgabe.

[TYPE_DESC]

Hinter TYPE_DESC verbirgt sich der Typ der Datenbankdatei. Hier unterscheidet man zwischen ROWS (Daten) und LOG (Transaktionsprotokoll). Insbesondere sollte man im Ergebnis darauf achten, ob z. B. mehrere Dateien vom Typ [LOG] bei einer Datenbank definiert wurden. Mehrere Protokolldateien sind nutzlos, da Transaktionsprotokolle IMMER sequentiell geschrieben werden!

[PHYSICAL_NAME]

Die Informationen über die physikalischen Dateinamen sind für Fragen der Performance von großer Bedeutung. Die Informationen beantworten folgende Fragen:

• Sind Datendateien von Protokolldateien getrennt?
• Werden mehrere Protokolldateien verwendet?
• Werden mehrere Datendateien verwendet?

In OLTP-Systemen mit sehr hohen Schreibvorgängen ist eine Trennung von Daten- und Protokolldateien sicherlich sinnvoll. Entgegen einem weit verbreiteten Irrglauben, dass Schreibvorgänge nicht sofort bei Speicherung von Datensätzen durchgeführt werden, wird die Protokolldatei IMMER SOFORT geschrieben. Ist ein System von vielen Schreibvorgängen betroffen, kann eine Aufteilung der Datenbankdateien und der Transaktionsprotokolldatei auf unterschiedliche Laufwerke eine deutliche Entlastung bringen! Die Verwendung mehrerer Protokolldateien ist nicht notwendig. Ein erhoffter Performancegewinn kann nicht realisiert werden, da Transaktionen immer seriell in die Protokolldatei geschrieben werden.

Die Verwendung mehrerer Datendateien kann das Schreiben von Daten beschleunigen, da Microsoft SQL Server das “Round Robin Verfahren” für das Schreiben von Daten verwendet. Hierzu hat Klaus Aschenbrenner (b | t) ein “SQL Quickie” erstellt, in dem er das Verfahren nicht nur sehr gut beschreibt sondern in einer entsprechenden Demo auch die Funktionsweise zeigt.

Ebenfalls einen Blick wert ist, ob die Systemdatenbank [TEMPDB] mehrere Datendateien verwendet. Um TEMPDB ranken sich viele Mythen und Empfehlungen und eine allgemeine Empfehlung ist die Verwendung von mehreren Dateien in TEMPDB, um bei der Erstellung von temporären Tabellen SGAM und GAM-Contention zu vermeiden. Hierzu hat Robert Davis (b | t) eine interessante Präsentation erstellt, in der er auch ein Skript liefert, dass mögliche Engpässe in TEMPDB ermittelt!

Wird ein Multi-Core System untersucht, kann relativ schnell herausgefunden werden, ob TEMPDB noch Anpassungen benötigt (Anzahl Dateien und einheitliche Größe).

[size_MB], [growth_MB], [used_MB], [is_percent_growth]

Die aus meiner Sicht wichtigsten Angaben betreffen die Größeneinstellung der Datenbanken. Die Information [size_MB] beschreibt die physikalische Größe der Datenbankdatei. Sind es mehrere Dateien, die eine Datenbankdatei betreffen, sollte vor allen Dingen darauf geachtet werden, ob sie eine identische Größe besitzen um “Hot Spots” beim “Round Robin Verfahren” zu vermeiden.

Der Spalte [growth_MB] ist aus verschiedenen Gründen erhöhte Aufmerksamkeit zu schenken; handelt es sich um eine LOG-Datei (Transaktionsprotokoll), sollte der Vergrößerungsintervall aus den folgenden Gründen nicht zu groß gewählt sein.

• Protokolldateien können nicht vom “Instant File Initialization” partizipieren. Zu diesem Thema habe ich in einen ausführlichen Blogartikel “Performancevorteile durch Instant File Initialization” die Vor- und Nachteile beschrieben.
• Zu große Initialgröße / Vergrößerungen führen zu mehr und größeren  VLF (Virtual Log File). Hierzu hat Kimberly L. Tripp (b | t) mit “Transaction Log VLFs – too many or too few” einen bemerkenswerten Artikel geschrieben, der die Vor- und Nachteile beschreibt.

Bei der Wahl der geeigneten Vergrößerung für Datendateien spielen viele Faktoren eine Rolle. Es gibt keine generelle Empfehlung für die Vergrößerung, da sie sehr stark vom Workload der Applikation abhängig ist, die diese Datenbank verwendet. Wenn es sich um eine “Grußkarten-Datenbank” handelt, sind Vergrößerungsintervalle von 100 GB sicherlich Unsinn, aber genau so unsinnig ist ein Vergrößerungsintervall von 1 MB für ein Onlineportal.

Bei den Vergrößerungsintervallen sollte darauf geachtet werden, dass nicht der Standardwert von 1 MB eingerichtet ist. Das führt zu hoher Fragmentierung der Datei auf der Disk und hat somit Einfluss auf die Performance. Im Zusammenhang mit den Größeneinstellungen ist auf jeden Fall “Instant File Initialization” zu beachten. Ist IFI nicht aktiviert, bedeutet Vergrößerung immer STILLSTAND!

Merke: Der beste Vergrößerungsintervall ist der, den man nicht anwenden muss. Bei der Größenbestimmung einer Datenbank sollte möglichst gleich im Vorfeld eine adäquate Größeneinstellung verwendet werden, die eine Vergrößerung sowohl von Daten- als auch von Protokolldatei erst gar nicht erfordert!

Ein Blick auf die Auswertungen in [used_MB] ist hilfreich, um festzustellen, wann die nächste Vergrößerung ansteht. Hier kann man unter Umständen vorbeugen, indem man die Datenbankdateien bereits vorher (in der Nacht) entsprechend vergrößert.

Last but not Least die Prüfung, ob die Datenbank prozentual vergrößert wird! Leider habe ich bei den bisherigen Begutachtungen von Microsoft SQL Server weit über 75% aller angetroffenen Datenbanken mit einem Vergrößerungsintervall von 10% angetroffen. Ursächlich hierfür ist die Systemdatenbank [model], die als Vorlage für neue Datenbanken verwendet wird.

Die “Standardeinstellungen” von [model] sollten möglichst sofort bei Inbetriebnahme des Microsoft SQL Server geändert werden. Sowohl der Vergrößerungsintervall von 1 MB für Datenbankdateien als auch der Wert von 10% für die Protokolldatei sind eher kontraproduktiv für ein schnelles Datenbanksystem!

Herzlichen Dank fürs Lesen!