tempdb richtig konfigurieren: Wenn die Datenbank gesund ist und trotzdem alles steht

Die Situation kennt jeder, der eine Sage 100 mit mehr als einer Handvoll Anwendern betreut. Vormittags läuft alles rund. Am Monatsende, wenn gleichzeitig Umlagerungen gebucht, Abrechnungen erstellt und drei Auswertungen gestartet werden, zieht sich die Anwendung plötzlich wie Kaugummi. Und das Ärgerliche daran: Die Anwendungsdatenbank ist nachweislich gesund. Die Indizes sind gepflegt, die Statistiken aktuell, der Performance-Test zeigt beim Storage und beim Netzwerk grünes Licht. Trotzdem stehen die Anwender.

In solchen Fällen lohnt der Blick auf eine Datenbank, um die sich sonst niemand kümmert, weil sie ja keine Nutzdaten enthält. Die tempdbTempDBSystemdatenbank des SQL Servers für temporäre Tabellen, Sortierungen, Hash-Operationen und weitere Zwischenergebnisse.Mehr im Glossar → ist nicht der Abstellraum des SQL Servers, sondern seine Werkbank. Und wenn sich an dieser Werkbank zwanzig Leute gleichzeitig um dasselbe Werkzeug streiten, wird jeder einzelne Vorgang langsam, ohne dass irgendeine Abfrage für sich betrachtet schlecht wäre.

In diesem Beitrag zeige ich Dir, was die Sage 100 dort überhaupt ablegt, wie Du das typische Stauzeichen im Wartbild eindeutig identifizierst und welche vier Einstellungen die Sache dauerhaft erledigen.

Was in der tempdb landet, ohne dass es jemand so programmiert hat

Der naheliegende Inhalt sind temporäre Tabellen und Tabellenvariablen, also alles mit # davor. Das ist aber der kleinere Teil. Den größeren erzeugt der SQL Server von sich aus, ohne dass es im Code sichtbar wäre:

• Arbeitstabellen für Sortierungen. Jedes ORDER BY und GROUP BY, das nicht komplett in den zugewiesenen Arbeitsspeicher passt, wird in der tempdb zwischengelagert. Genau das passiert bei Listen über mehrere Jahre Belegdaten regelmäßig.
• Hash-Tabellen für Joins und Aggregate. Verknüpft der Optimizer zwei große Mengen per Hash Join, baut er die Hash-Tabelle im Speicher auf und lagert sie bei Platzmangel in die tempdb aus. Im Ausführungsplan heißt das dann Hash Warning beziehungsweise Spill.
• Spools. Zwischenergebnisse, die ein Plan mehrfach braucht, legt der SQL Server als Spool ab, und zwar in der tempdb.
• Der Versionsspeicher. Das ist der Punkt, der in Sage-Umgebungen gern unterschätzt wird. Seit SQL Server 2005 werden die Tabellen inserted und deleted in DML-Triggern nicht mehr aus dem Transaktionsprotokoll rekonstruiert, sondern aus dem Versionsspeicher in der tempdb bedient. Jeder Trigger, der beim Buchen oder beim Speichern eines Belegs feuert, schreibt also in die tempdb.

Dazu kommt eine Eigenschaft, die man kennen muss, um die Konfiguration zu verstehen: Die tempdb wird bei jedem Start der Instanz komplett neu erstellt, als Kopie der model-Datenbank und mit genau den Größen, die in der Dateikonfiguration hinterlegt sind. Ein Backup gibt es nicht und braucht es nicht. Sie läuft immer im Wiederherstellungsmodell Simple, daran gibt es nichts einzustellen. Praktisch bedeutet das aber auch: Jede Größe, die Du nicht konfiguriert hast, muss sich der Server nach jedem Neustart im laufenden Betrieb erst wieder erarbeiten.

Warum gerade die Sage 100 die tempdb fordert

Ein ERP ist für den SQL Server ein unangenehmes Mischprofil. Auf der einen Seite stehen sehr viele sehr kurze Schreibvorgänge aus der Belegerfassung und der Buchung, auf der anderen Seite einige sehr lange Leseläufe aus Auswertungen, Listen und Umlagerungen. Beides gleichzeitig, von denselben Anwendern, auf denselben Tabellen.

Für die tempdb heißt das konkret: Die kurzen Schreibvorgänge füllen über die Trigger permanent den Versionsspeicher, die langen Leseläufe fordern gleichzeitig großzügig Arbeitstabellen an. Und weil Monatsende bei allen Anwendern am gleichen Tag ist, passiert das nicht gleichmäßig verteilt, sondern geballt. Der Effekt, den Du dann siehst, ist kein Platzproblem. Es ist ein Verwaltungsproblem, und das hat einen Namen.

Allocation Contention, oder: Der Streit um die Verwaltungsseiten

Wenn eine Sitzung in der tempdb Platz für eine temporäre Tabelle braucht, muss der SQL Server ihr Seiten zuweisen. Welche Seiten innerhalb einer Datei frei sind, steht nicht irgendwo in einer Tabelle, sondern auf speziellen Verwaltungsseiten in der Datei selbst:

Jede dieser Seiten muss beim Ändern kurz exklusiv gehalten werden, über einen sogenannten Latch. Das ist keine Sperre im Sinne von Blocking, sondern ein sehr kurzlebiger Schutz im Arbeitsspeicher, der normalerweise Mikrosekunden dauert. Nur: Wenn fünfzig Sitzungen gleichzeitig Platz anfordern und es in der Datenbank nur eine einzige Datei gibt, dann gibt es auch nur eine PFS-Seite, eine GAM und eine SGAM, um die sich alle anstellen müssen. Aus Mikrosekunden werden Millisekunden, und das bei jedem Anlegen einer temporären Tabelle erneut.

Das ist Allocation Contention. Sie zeigt sich als Wartevorgang vom Typ PAGELATCH_UP oder PAGELATCH_EX auf einer Ressource in der Datenbank mit der ID 2. Die 2 ist immer die tempdb.

So siehst Du es auf Deinem Server

Erster Schritt, das Gesamtbild aus den Wait StatisticsWait StatisticsSQL-Server-Messwerte darüber, worauf Prozesse während ihrer Ausführung warten, beispielsweise Storage, Sperren oder Netzwerk.Mehr im Glossar →. Diese Abfrage zeigt, worauf Deine Instanz seit dem letzten Start am meisten gewartet hat, ohne die Leerlauf-Wartetypen, die sonst immer oben stehen würden:

SELECT TOP 15
       wait_type,
       waiting_tasks_count                                    AS anzahl,
       wait_time_ms / 1000                                    AS wartezeit_s,
       CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER ()
            AS DECIMAL(5,2))                                  AS anteil_prozent
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0
  AND wait_type NOT IN (
      'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
      'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
      'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
      'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT',
      'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_WAIT',
      'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
      'DIRTY_PAGE_POLL', 'SP_SERVER_DIAGNOSTICS_SLEEP', 'QDS_ASYNC_QUEUE',
      'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', 'FT_IFTS_SCHEDULER_IDLE_WAIT')
ORDER BY wait_time_ms DESC;

Steht hier PAGELATCH_UP oder PAGELATCH_EX weit oben, lohnt der zweite Schritt. Der muss während der Last laufen, also genau dann, wenn die Anwender sich beschweren:

SELECT r.session_id,
       r.wait_type,
       r.wait_resource,
       r.wait_time                AS wartezeit_ms,
       DB_NAME(r.database_id)     AS datenbank,
       SUBSTRING(t.text, 1, 200)  AS statement
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.wait_type LIKE 'PAGELATCH%'
  AND r.wait_resource LIKE '2:%'
ORDER BY r.wait_time DESC;

Die Spalte wait_resource ist hier der Beweis. Sie hat das Format Datenbank-ID, Datei-ID, Seiten-ID. Beispiele, die Du sehen wirst:

• 2:1:1 oder 2:3:8088 steht für eine PFS-Seite. Das ist der häufigste Fall.
• 2:1:2 steht für die GAM.
• 2:1:3 steht für die SGAM. Das tritt vor allem auf älteren Versionen auf, weil dort für die ersten acht Seiten eines Objekts noch gemischte Extents verwendet wurden.

Wenn mehrere Sitzungen gleichzeitig auf solchen Seiten warten, hast Du Deine Diagnose. Und der dritte Blick zeigt Dir, wer den Platz überhaupt verbraucht:

SELECT SUM(user_object_reserved_page_count)     * 8 / 1024 AS temp_tabellen_mb,
       SUM(internal_object_reserved_page_count) * 8 / 1024 AS sortierungen_mb,
       SUM(version_store_reserved_page_count)   * 8 / 1024 AS versionsspeicher_mb,
       SUM(unallocated_extent_page_count)       * 8 / 1024 AS frei_mb
FROM tempdb.sys.dm_db_file_space_usage;

Diese Zahlen sind aussagekräftiger als die reine Dateigröße. Viel unter sortierungen_mb deutet auf Auswertungen hin, die mehr Arbeitsspeicher bräuchten oder bessere Indizes. Ein großer Versionsspeicher deutet auf lange offene Transaktionen hin, und damit sind wir bei einem Thema, das wir im Beitrag zum Blocking ausführlich behandelt haben.

Maßnahme 1: Mehrere gleich große Datendateien

Das ist der wirksamste Hebel und gleichzeitig der am häufigsten fehlende. Die Logik ist einfach: Mehr Dateien bedeuten mehr Sätze von Verwaltungsseiten, und der SQL Server verteilt die Anforderungen über alle Dateien. Statt einer Warteschlange gibt es dann acht.

Microsoft nennt in den Empfehlungen zur tempdb eine klare Faustregel. Bei bis zu acht logischen Prozessoren legst Du so viele Datendateien an, wie Du logische Prozessoren hast. Bei mehr als acht beginnst Du mit acht und erhöhst in Schritten von vier, solange die Wartevorgänge nicht verschwinden. Ein Server mit acht Kernen bekommt also acht Dateien, ein Server mit sechzehn Kernen startet ebenfalls mit acht und geht bei Bedarf auf zwölf.

Entscheidend ist dabei der zweite Teil, und der wird oft übersehen. Alle Dateien müssen gleich groß sein und dasselbe Wachstum konfiguriert haben. Der SQL Server verteilt neue Seiten nämlich nicht gleichmäßig, sondern nach dem Proportional Fill Algorithmus, also proportional zum freien Platz je Datei. Eine Datei mit 8 GB und sieben Dateien mit je 100 MB führen dazu, dass praktisch alles in der großen Datei landet. Du hast dann acht Dateien und trotzdem eine Warteschlange.

Zuerst den Istzustand ansehen:

SELECT file_id,
       name,
       type_desc,
       size / 128.0 AS groesse_mb,
       CASE WHEN is_percent_growth = 1
            THEN CAST(growth AS VARCHAR(10)) + ' %'
            ELSE CAST(growth / 128 AS VARCHAR(10)) + ' MB'
       END          AS wachstum,
       physical_name
FROM tempdb.sys.database_files
ORDER BY type_desc DESC, file_id;

Und dann angleichen beziehungsweise ergänzen. Hier am Beispiel von vier Dateien mit je 2 GB auf einem eigenen Laufwerk:

-- bestehende Datei auf Zielgröße und festes Wachstum setzen
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'tempdev', SIZE = 2048MB, FILEGROWTH = 256MB);

-- weitere Dateien ergänzen, identisch konfiguriert
ALTER DATABASE tempdb
ADD FILE (NAME = 'tempdev2', FILENAME = 'T:\tempdb\tempdb_mssql_2.ndf',
          SIZE = 2048MB, FILEGROWTH = 256MB);

ALTER DATABASE tempdb
ADD FILE (NAME = 'tempdev3', FILENAME = 'T:\tempdb\tempdb_mssql_3.ndf',
          SIZE = 2048MB, FILEGROWTH = 256MB);

ALTER DATABASE tempdb
ADD FILE (NAME = 'tempdev4', FILENAME = 'T:\tempdb\tempdb_mssql_4.ndf',
          SIZE = 2048MB, FILEGROWTH = 256MB);

Neue Dateien stehen sofort zur Verfügung. Eine bestehende Datei zu verkleinern, um sie an die anderen anzugleichen, funktioniert im laufenden Betrieb dagegen oft nicht zuverlässig, aus genau den Gründen, die im Beitrag zu DBCC SHRINKFILE stehen. Der saubere Weg ist, die Zielgrößen zu hinterlegen und die Instanz im nächsten Wartungsfenster neu zu starten. Beim Start wird die tempdb neu erstellt, und zwar genau mit den konfigurierten Werten.

Maßnahme 2: Feste Startgröße statt Autogrowth-Treppe

Die zweite Maßnahme kostet nichts außer Plattenplatz und wird am häufigsten falsch gemacht. Wenn die tempdb mit 8 MB startet und in Prozentschritten wächst, dann vergrößert der SQL Server die Datei im laufenden Betrieb immer wieder, mitten unter Last, und zwar in immer größeren Schritten. Jedes dieser Wachstumsereignisse hält die betroffenen Vorgänge kurz an.

Besonders unangenehm ist das nach jedem Neustart, weil die tempdb dann wieder bei der konfigurierten Startgröße anfängt. Der erste Monatsabschluss nach dem Patchday fühlt sich dadurch schlechter an als der zweite, ohne dass sich sonst etwas geändert hätte.

Richtig ist deshalb: Die Startgröße so setzen, wie Du die tempdb im Betrieb tatsächlich brauchst, und das Wachstum als festen Wert in Megabyte, nicht in Prozent. Als Anhaltspunkt für eine mittlere Sage-100-Umgebung sind insgesamt 8 bis 16 GB üblich, verteilt auf die Dateien, plus bewusste Reserve auf dem Laufwerk für Ausnahmeläufe. Besser als jeder Richtwert ist aber der Blick auf die eigene Historie. Schau nach einem Monatsabschluss nach, wie groß die tempdb tatsächlich geworden ist, und konfiguriere diesen Wert als Startgröße.

Zwei Ergänzungen, die dabei helfen. Erstens gehört das Dienstkonto des SQL Servers in die Richtlinie Durchführen von Volumenwartungsaufgaben. Dann nutzt der Server die Instant File Initialization und muss neue Bereiche in Datendateien nicht erst mit Nullen überschreiben, was sowohl den Start als auch jedes Wachstum deutlich beschleunigt. Für Protokolldateien gilt das nicht, dort wird weiterhin genullt. Zweitens braucht die tempdb genau eine Protokolldatei. Mehrere bringen hier nichts, weil das Protokoll ohnehin sequenziell geschrieben wird.

Maßnahme 3: Eine eigene Platte

Die tempdb teilt sich auf vielen Servern das Laufwerk mit den Anwendungsdatenbanken, manchmal sogar mit dem Betriebssystem. Das ist deshalb unglücklich, weil ihr Zugriffsmuster ein ganz anderes ist. Sie schreibt viel, sie schreibt in Schüben, und sie braucht die Daten nur für die Dauer einer Abfrage.

Daraus folgt eine Empfehlung, die wir schon im Beitrag zur Performance des Applikationsservers für die Datenbankdateien gegeben haben, und für die tempdb gilt sie doppelt: Eigenes Laufwerk, möglichst auf NVMe oder SSD. In virtualisierten Umgebungen heißt das auch, nicht alles auf dasselbe Datastore zu legen, nur weil dort gerade Platz ist.

Ein praktischer Nebeneffekt dieser Trennung: Weil die tempdb keine Daten über einen Neustart hinweg aufbewahrt, braucht sie keinen Schutz gegen Datenverlust im Sinne eines Backups. Lokaler, schneller Speicher ist hier also legitim, auch wenn er nicht Teil der Sicherungsstrategie ist. Was sie allerdings sehr wohl braucht: Das konfigurierte Verzeichnis muss beim Start existieren und das Dienstkonto muss darauf schreiben dürfen. Fehlt eines von beiden, startet die Instanz nicht. Wer die tempdb verschiebt, prüft diese zwei Punkte besser vor dem Neustart als danach.

-- Verschieben: Pfad ändern, dann Dienst neu starten
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'tempdev', FILENAME = 'T:\tempdb\tempdb.mdf');

ALTER DATABASE tempdb
MODIFY FILE (NAME = 'templog', FILENAME = 'T:\tempdb\templog.ldf');

Taucht nach der Umstellung weiterhin PAGEIOLATCH_SH oder PAGEIOLATCH_EX auf Datenbank 2 auf, ist das kein Verwaltungsproblem mehr, sondern ein echtes Storage-Thema. Dann gehört die Latenz des Laufwerks gemessen, und zwar mit Zahlen.

Maßnahme 4: Nur wenn nötig, die Metadaten in den Speicher

Es gibt eine zweite Art von Konkurrenz, die mit zusätzlichen Dateien nicht verschwindet. Sie betrifft nicht die Verwaltungsseiten der Dateien, sondern die Systemtabellen der tempdb selbst, also die Buchführung darüber, welche temporären Objekte gerade existieren. Erkennbar ist sie an PAGELATCH-Wartevorgängen auf Datenbank 2, deren Seiten aber nicht zu PFS, GAM oder SGAM gehören.

Ab SQL Server 2019 lässt sich diese Buchführung in speicheroptimierte Tabellen verlegen:

ALTER SERVER CONFIGURATION
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Die Umstellung wird erst nach einem Neustart der Instanz aktiv und kostet zusätzlichen Arbeitsspeicher. Sie ist eine gezielte Maßnahme gegen ein nachgewiesenes Metadatenproblem, kein allgemeiner Beschleuniger. Bei einer Standardsoftware wie der Sage 100 würde ich sie deshalb erst dann einschalten, wenn die drei vorherigen Punkte erledigt sind, die Wartevorgänge belegt sind und die Änderung vorher in einer Testumgebung gelaufen ist. Dasselbe gilt für die Überlegung, auf SQL Server 2022 zu gehen, wo Microsoft die gleichzeitige Aktualisierung von PFS, GAM und SGAM eingebaut hat und damit an genau dieser Stelle noch einmal Druck herausnimmt.

Was nicht hilft

Die Reihenfolge für die Praxis

1. Wartbild der Instanz aufnehmen und prüfen, ob PAGELATCH_UP oder PAGELATCH_EX überhaupt relevant sind.
2. Unter Last messen und über wait_resource bestätigen, dass es die tempdb und die Verwaltungsseiten sind.
3. Dateikonfiguration dokumentieren, also Anzahl, Größen und Wachstum, und mit der Zahl der logischen Prozessoren vergleichen.
4. Zielgrößen festlegen, Dateien ergänzen und alle Dateien identisch konfigurieren, Wachstum in Megabyte statt Prozent.
5. Instant File Initialization über die Richtlinie für Volumenwartungsaufgaben freigeben.
6. tempdb auf ein eigenes schnelles Laufwerk legen, Verzeichnis und Berechtigung vor dem Neustart prüfen.
7. Im Wartungsfenster neu starten, damit die tempdb mit den neuen Werten entsteht.
8. Nach dem nächsten Monatsabschluss erneut messen und erst dann über weitere Dateien oder speicheroptimierte Metadaten entscheiden.

Fazit

Die tempdb ist der blinde Fleck in vielen Sage-100-Installationen, weil sie keine Nutzdaten enthält und deshalb in keiner Sicherungsstrategie und in keinem Wartungsplan auftaucht. Gleichzeitig läuft über sie jede Sortierung, jeder größere Join und über die Trigger auch jeder Buchungsvorgang. Eine einzelne Datei mit prozentualem Wachstum auf dem Systemlaufwerk ist damit eine Engstelle, die sich bei zehn Anwendern nicht zeigt und bei fünfzig plötzlich den ganzen Betrieb bestimmt.

Das Gute daran: Die Diagnose ist eindeutig und die Maßnahmen sind überschaubar. Ein Blick ins Wartbild, ein Blick auf wait_resource, dann mehrere gleich große Dateien, feste Größen, eine eigene Platte und ein Neustart im Wartungsfenster. Das ist eine der wenigen Stellen am SQL Server, an denen so wenig Aufwand so viel Spürbares bewegt.

Das Tool kostenlos herunterladen

Die Wartevorgänge der Instanz, die Dateikonfiguration und die Latenz der beteiligten Laufwerke liest unser SQL-Performance-Test automatisch mit aus und bewertet sie. Du bekommst das komplette Script inklusive grafischer Oberfläche kostenlos gegen eine Newsletter-Anmeldung. Was die einzelnen Messwerte bedeuten, erklärt der ausführliche Beitrag zum Tool.

Unklar, wo die Zeit bleibt? Wir messen das.

Wenn Deine Sage 100 unter Last einbricht und Du nicht sicher bist, ob es an der tempdb, am Storage, am Netzwerk oder an einer einzelnen Auswertung liegt, nehmen wir Deinen Server auf und sagen Dir, wo die Zeit tatsächlich verloren geht. Einen Überblick über das Vorgehen gibt unser Sage 100 Health-Check, und auch bei allen anderen SQL-Server-Themen rund um die Sage 100 sind wir gern Dein Ansprechpartner.