Optimieren der Leistung von 'tempdb'

Die Größe und die physische Platzierung der tempdb-Datenbank kann sich auf die Leistung eines Systems auswirken. Wurde für tempdb beispielsweise eine zu kleine Größe definiert, muss bei jedem Neustart der SQL Server-Instanz möglicherweise ein Teil der Verarbeitungslast des Systems dafür aufgewendet werden, die tempdb-Datenbank automatisch auf den Umfang zu vergrößern, der zum Unterstützen der anfallenden Arbeitsauslastung erforderlich ist. Sie können diesen zusätzlichen Aufwand vermeiden, indem Sie die tempdb-Daten- und Protokolldatei vergrößern. Informationen zum Bestimmen des für tempdb erforderlichen Speicherplatzes finden Sie unter Kapazitätsplanung für tempdb.

Empfehlungen zur Größe und Platzierung von "tempdb"

Um eine optimale Leistung von tempdb zu erreichen, wird die folgende Konfiguration für tempdb in einer Produktionsumgebung empfohlen:

  • Legen Sie das Wiederherstellungsmodell von tempdb auf SIMPLE fest. Bei diesem Modell wird der Protokollspeicherplatz automatisch wieder freigegeben, um die Speicherplatzanforderungen möglichst gering zu halten.

    Weitere Informationen finden Sie unter ALTER DATABASE (Transact-SQL) oder Vorgehensweise: Anzeigen oder Ändern eines Wiederherstellungsmodells einer Datenbank (SQL Server Management Studio).

  • Lassen Sie zu, dass die tempdb-Dateien automatisch nach Bedarf vergrößert werden. Die Datei kann dadurch so lange vergrößert werden, bis der Datenträger voll ist.

    HinweisHinweis

    Wenn für die Produktionsumgebung das Risiko von Anwendungstimeouts, die während der automatischen Vergrößerungsvorgänge auftreten können, nicht tragbar ist, weisen Sie vorab Speicherplatz für die erwartete Arbeitsauslastung zu.

  • Legen Sie das Inkrement für die Dateivergrößerung auf eine sinnvolle Größe fest, sodass der Zuwachs der tempdb-Datenbank nicht zu gering ausfällt. Wenn der Dateizuwachs im Vergleich zur Anzahl der Daten, die in die tempdb-Datenbank geschrieben werden, zu gering ist, muss tempdb möglicherweise ständig vergrößert werden. Dies beeinträchtigt die Leistung. Beim Festlegen des FILEGROWTH-Inkrements für tempdb-Dateien sollten die folgenden Richtlinien eingehalten werden:

    Größe der tempdb-Datei

    FILEGROWTH-Inkrement

    0 bis 100 MB

    10 MB

    100 bis 200 MB

    20 MB

    ab 200 MB

    10%*

    * Möglicherweise müssen Sie diesen Prozentwert an die Geschwindigkeit des E/A-Subsystems anpassen, auf dem sich die tempdb-Dateien befinden. Um mögliche Latchtimeouts zu vermeiden, sollten Sie den Vorgang für eine automatische Vergrößerung auf etwa zwei Minuten beschränken. Beispiel: Wenn das E/A-Subsystem eine Datei mit 50 MB pro Sekunde initialisieren kann, sollte das FILEGROWTH-Inkrement unabhängig von der Größe der tempdb-Datei auf maximal 6 GB festgelegt werden. Verwenden Sie nach Möglichkeit die sofortige Datenbankdateiinitialisierung, um die Leistung von automatischen Vergrößerungsvorgängen zu verbessern.

  • Weisen Sie allen tempdb-Dateien im Voraus Speicherplatz zu, indem Sie die Dateigröße auf einen Wert festlegen, der hoch genug ist, um der typischen Arbeitsauslastung in der Umgebung gerecht zu werden. Dadurch verhindern Sie, dass die tempdb-Datenbank zu häufig vergrößert wird, was zu Leistungseinbußen führen kann. Für die tempdb-Datenbank sollte die automatische Vergrößerung festgelegt werden, jedoch nur für den Fall eines zusätzlichen Speicherplatzbedarfs für nicht geplante Ausnahmen.

  • Erstellen Sie so viele Dateien, wie für die Maximierung der Bandbreite des Datenträgers erforderlich sind. Durch Verwenden mehrerer Dateien werden tempdb-Speicherkonflikte reduziert, und die Skalierbarkeit wird deutlich verbessert. Erstellen Sie jedoch nicht zu viele Dateien, da dies die Leistung reduzieren und den Verwaltungsaufwand erhöhen kann. Als allgemeine Richtlinie sollten Sie für jede CPU auf dem Server (unter Berücksichtigung der Einstellungen für die Option affinity mask) eine Datendatei erstellen und dann die Anzahl von Dateien bei Bedarf nach oben oder unten anpassen. Beachten Sie, dass eine Dual-Core-CPU als zwei CPUs betrachtet wird.

  • Legen Sie für jede Datendatei die gleiche Größe fest. Dies ermöglicht eine optimale Leistung beim proportionalen Auffüllen der Dateien.

  • Platzieren Sie die tempdb-Datenbank auf einem schnellen E/A-Subsystem. Verwenden Sie Datenträgerstriping, wenn viele Datenträger direkt angeschlossen sind.

  • Legen Sie die tempdb-Datenbank auf anderen Datenträgeren ab als denen, die von den Benutzerdatenbanken verwendet werden.

Ändern der tempdb-Größen- und Vergrößerungsparameter

Sie können die Größen- und Dateivergrößerungsparameter der tempdb-Daten- oder Protokolldateien mit einer der folgenden Methoden ändern:

Die Werte für die Dateigrößen- und Dateivergrößerungsparameter werden bei jedem Erstellen von tempdb verwendet. Wenn Sie z. B. die Größe der tempdb-Datendatei auf 20 MB und das Inkrement für die Dateivergrößerung auf 15 % erhöhen, werden die neuen Werte sofort übernommen. Wenn sich durch nachfolgende Transaktionsaktivitäten die Größe der tempdb-Datenbank erhöht, wird die Datendatei jedes Mal, wenn Sie die Instanz von SQL Server neu starten, auf die Größe von 20 MB zurückgesetzt.

Anzeigen der tempdb-Größen- und Vergrößerungsparameter

Sie können die Größen- und Dateivergrößerungsparameter der tempdb-Daten- oder Protokolldateien mit einer der folgenden Methoden anzeigen:

  • SQL Server Management Studio

  • Ausführen der folgenden Abfrage.

    SELECT 
        name AS FileName, 
        size*1.0/128 AS FileSizeinMB,
        CASE max_size 
            WHEN 0 THEN 'Autogrowth is off.'
            WHEN -1 THEN 'Autogrowth is on.'
            ELSE 'Log file will grow to a maximum size of 2 TB.'
        END,
        growth AS 'GrowthValue',
        'GrowthIncrement' = 
            CASE
                WHEN growth = 0 THEN 'Size is fixed and will not grow.'
                WHEN growth > 0 AND is_percent_growth = 0 
                    THEN 'Growth value is in 8-KB pages.'
                ELSE 'Growth value is a percentage.'
            END
    FROM tempdb.sys.database_files;
    GO
    

Erkennen von Datenträger-E/A-Pfadfehlern

Wenn diese Option auf CHECKSUM festgelegt wurde, entdeckt die Option PAGE_VERIFY Datenbankseiten, die durch Datenträger-E/A-Pfadfehler beschädigt wurden, wie z. B. MSSQLSERVER_823, MSSQLSERVER_824 oder MSSQLSERVER_825, im SQL-Fehlerprotokoll. Datenträger-E/A-Pfadfehler können die Ursache von Datenbankbeschädigungen sein und werden im Allgemeinen durch Stromausfälle oder Datenträger-Hardwarefehler verursacht, die beim Schreiben der Seite auf den Datenträger auftreten. Weitere Informationen zu E/A-Fehlern finden Sie unter Microsoft SQL Server I/O Basics, Chapter 2 (in Englisch).

In früheren Versionen von SQL Server ist die Datenbankoption PAGE_VERIFY für die tempdb-Datenbank auf NONE festgelegt und kann nicht geändert werden. In SQL Server 2008 ist der Standardwert für die tempdb-Datenbank CHECKSUM für neue Installationen von SQL Server. Bei der Aktualisierung einer Installation von SQL Server bleibt der Standardwert NONE. Für die tempdb-Datenbank sollten Sie die Option PAGE_VERIFY auf CHECKSUM festlegen.