MS-SQL TempDB

Aus Software Entwicklung Projekte
Wechseln zu: Navigation, Suche

TempDB

Warum soll sollte die TempDB optimiert werden?

Die tempDB wird automatisch von verschiedenen Operationen verwendet, unabhängig, mit welcher DB gerade gearbeitet wird.

Typische Operationen sind z.B. :

  • Temp tabellen (# vor dem Tabellennamen)
  • Globale temp Tabellen (## vor dem Tabellennamen)
  • Sortierung von großen Joins
  • Sortierung von Indizies, falls die entsprechende Option bei dem Indize gesetzt ist.
  • ...

Dadurch kann die tempDB zu einem Flaschenhals werden, wodurch hohe Waits entstehen. In diesen Fällen sollte die tempDB optimiert werden.

Optimierung der TempDB

Am besten zuerst das weiter unten erklärte Skript als Extended Event anlegen, damit die tempDB Contention gemessen werden kann.

Die tempDB kann über die Anzahl der Data Files optimiert werden. Die Anzahl der Transaktionslogs sollte immer genau 1 betragen, da diese so oder so seriell beschrieben werden. Mehr als genau eine bringt hier keinen Vorteil.

Jedes einzelne tempDB Data File sollte genau die gleiche Größe haben, wie alle anderen. Auch die Einstellungen für Autogrowth sollten entweder für ganz ausgeschaltet werden oder wenigstens alle genau die gleichen Einstellungen haben.

Wenn tempDB Contention in einer signifikanten Menge vorhanden ist, kann diese durch hinzufügen von neuen Datafiles verringert werden. Optimaler weise sollten so viele Datafiles hinzugefügt werden, bis keine signifikante Verbesserung bei der Messung der tempDB Contention auftritt.

Als Faustformel kann die Anzahl der CPU Cores genommen werden. Aber Achtung, jedes neue Datafile erhöht auch die Verwaltungskosten. Also nur so viele wie nötig, aber nicht zu viele.

Speziell bei System mit mehr als 8 Cores sollte genau gemessen werden, ob noch mehr Datafiles wirklich Sinn machen.

Desweiteren sollte im Verlauf kontrolliert werden, dass nicht genau ein Datafile übermäßig stark wächst und die Größe der einzelnen Datafile gleich bleibt.

Wird die tempDB intensiv genutzt sollte diese auch auf einen möglichst schnelle Festplatten ausgelagert werden. Datensicherheit ist hier nur bedingt wichtig, da sie automatisch neu erzeugt wird, sobald die SQL Server Instanz neu gestartet wird.

Messung der TempDB Contention mittels Extended Events

Berechnung der einzelnen Pagearten

Page arten

  • PFS : Page Free Space/Verwaltung der freien Pages
  • GAM : Global Allocation Map
  • SGAM : Shared Global Allocation Map

PageID's

  • PageID 1 : PFS
  • PageID 2 : GAM
  • PageID 3 : SGAM
  • PageID / 8088 : PFS
  • PageID / 511232 : GAM
  • (PageID-1) / 511232 : SGAM

D.h. auf jede weitere GAM Page kommt genau eine SGAM Page

Magische Nummer Mode

Im Skript taucht die Nummer "mode=2 OR mode=3" auf. Wir wollen mit dem Skript nur die speziellen Arten "UP" und "SH" erfassen. Bis jetzt habe ich noch keinen Server gefunden, wo die beiden Arten nicht 2 oder 3 entsprechen. Mit folgendem T-SQL Skript können die Werte kontrolliert werden:

SELECT map_key, map_value FROM sys.dm_xe_map_values
WHERE name = N'latch_mode' AND map_value IN (N'UP', N'SH');

Anlegen des Extended Events für das Überwachen der TempDB Contention

Achtung: Das Script funktioniert nur für TempDB Data Files bis 20GB. Für grössere TempDB Datafiles müssen die SGAM PageID's erweitert werden.

Desweiteren sollte der Pfad für das *.xel File noch gesetzt werden.

Uns interessieren auch nur die Fälle, wo ein Latch warten musste, weil die entsprechende Page durch einen anderen Latch geblockt wurde, daher "duration>0".

Der Wert "database_id=2" begrenzt den Event auf die TempDB, da diese normalerweise die ID 2 erhält.

CREATE EVENT SESSION TempdbContentionMonitor ON SERVER 
ADD EVENT sqlserver.latch_suspend_end
(
    WHERE ( database_id=2 AND duration>0 AND 
           (mode=2 OR mode=3) AND 
           (page_id<4 OR 
               package0.divides_by_uint64(page_id, 8088) OR 
               package0.divides_by_uint64(page_id, 511232) OR  
               page_id=511233 OR page_id=1022465 OR 
               page_id=1533697 OR page_id=2044929
           )
       )
)
ADD TARGET package0.event_file(
SET filename='Logpfad...\TempdbContentionMonitor.xel',
    max_file_size=50, max_rollover_files=20, increment=5)
WITH (STARTUP_STATE=ON);

Warum so kompliziert?

Der Extended Event könnte auch einfach die Contention auf alle Pages messen. Das Ergebnis kann auch für die Optimierung der TempDB verwendet werden.

Das Ergebnis wäre aber verfälscht. Z.B. bei globalen Tabellen in der tempdb, die durch mehrere Transaktionen angesprochen werden, können "Waits" auftreten, die sich nicht durch diese tempdb Optimierung verbessern lassen.