Ein paar Hundert SQL-Server Instanzen (und viele andere Server) werden vom kundeninternen Betriebsteam mit Nagios überwacht. Das Team hat sich beschwert, dass auf einigen SQL Servern die Überwachungsskripte sporadisch spürbar länger brauchen. Dabei sollen die Skripte sehr harmlos sein und nur ein paar Metriken wie “Verfügbarer Speicher in DB” oder “Größe der Log-Datei” abfragen.

Nagios ist ein Open Source Monitoring Framework (vergleichbar mit SCOM, System Center Operations Manager von Microsoft), das durch diverse Plug-Ins zur Überwachung von unterschiedlichen Diensten (DNS, DHCP, Exchange), unter anderem auch SQL Server Instanzen erweitert werden kann.

Die Abfrage des Views sys.dm_tran_locks hat ergeben, dass das Skript auf eine Ressource vom Typ KEY in tempdb wartet. Warum ausgerechnet der Zugriff auf tempdb ein Problem ist, wurde beim Blick auf den Skriptcode klar.

Folgendes Konstrukt führt zu Locks auf tempdb Objekten:

IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE name LIKE '#LogSpaceStats%')

BEGIN

    DROP TABLE #LogSpaceStats;

END;

Dieses Muster kommt leider relativ häufig vor und wird immer wieder auf Blogs und Foren empfohlen (z.B. SQL Authority oder MSDN Forum). Mit folgendem Versuch kann man im SQL Server Management Studio nachvollziehen, dass das Anlegen einer temporären Tabelle in einer Transaktion die oben erwähnte Abfrage blockt:

Tab/Session 1

BEGIN TRAN

SELECT object_id INTO #harmlose_temp_tabelle FROM sys.objects;

/* Oder CREATE TABLE #harmlose_temp_tabelle(objectid INT);*/

-- ROLLBACK

Tab/Session 2

IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE name LIKE '#LogSpaceStats%')

BEGIN

    DROP TABLE #LogSpaceStats;

END;

PRINT 'Erst jetzt fertig'

Das SELECT-Statement muss dabei die Systemtabelle sysschobjs lesen und wird geblockt, wenn in einer anderen Transaktion ein temporäres Objekt angelegt oder gelöscht wird.

Außer diesem unschönen Verhalten mit unnötigen Locks hat das Konstrukt andere Mankos:

• Offensichtlich nicht parallel ausführbar, da #LogSpaceStats-Tabellen in anderen Sessions auch gefunden werden. Der Versuch die temporäre Tabelle in der aktuellen Session zu droppen schlägt dann fehl.
• Findet auch andere temporäre Tabellen (falls so wie oben die sysobjects DMV angesprochen wird, auch andere Objekte wie z.B. temporäre SPs), die überhaupt nichts mit dem Skript zu tun haben. Z.B. eine temporäre Tabelle “#LogSpaceStats_this_is_not_the_table_you_are_looking_for” wird mit dem Pattern ‘#LogSpaceStats%’ auch gefunden. Der DROP-Versuch schlägt dann fehl.

Folgende Prüfung braucht so gut wie keine Locks und besitzt auch keine der zwei oben erwähnten Schwächen:

IF OBJECT_ID('tempdb..#LogSpaceStats') IS NOT NULL

BEGIN

    DROP TABLE # LogSpaceStats;

END;

SQL Server 2016 (aktuell als CTP 3.2) bietet noch mehr Komfort und unterstützt die Syntax DROP TABLE IF EXISTS …, damit werden die oben genannten Probleme früher oder später der Vergangenheit angehören.

Blick über den Tellerrand:

Oracle hat auch ein Konzept von temporären Tabellen. Sie werden allerdings deutlich seltener in expliziter Form eingesetzt als in SQL Server, da Oracle viele Probleme, für die in SQL Server temporäre Tabellen verwendet werden, anders löst (z.B. mit Subquery Materialisierung, was aber auch nicht immer automagisch funktioniert).

Auch die Funktionalität von temporären Tabellen unterscheidet sich. So werden sie in der Regel statisch deklariert und können die Daten entweder bis zum nächsten COMMIT (ON COMMIT DELETE ROWS) oder bis die Session geschlossen wird (ON COMMIT PRESERVE ROWS) persistieren. Gleichzeitige Sessions sehen natürlich nur eigene Daten.

Dadurch, dass die Tabelle vorab deklariert wird und nicht ständig gedropped und neu angelegt werden muss, vermeidet man eine unschöne Vermischung von DDL (CREATE/DROP) und DML (INSERT/UPDATE/DELETE) Operationen in einer Ausführung.