Über den Sinn und Unsinn von UDFs

„Code duplizieren ist unschön, dilettantisch, ja fast schon unästhetisch und ist beim nächsten Refactoring zwingend zu eliminieren!“ In etwa so hört es sich bei professionellen SW-Entwicklern an, wenn diese über „schöne“ Software diskutieren.

Wenn wir in T-SQL Code duplizieren nicht möchten, greifen wir zu User Defined Functions (UDF). Dass UDFs aber nicht immer sinnvoll sind und wahre Performance Killer sein können, möchte ich dir anhand eines Erlebnisses bei einem meiner Kunden schildern:

In einem Migrationsprojekt müssen Daten aus mehreren AS400 Applikationen gelesen, validiert, transformiert und in einem SQL Server abgelegt werden. Also ein klassischer ETL-Prozess. Aus diversen Gründen haben wir uns für reine T-SQL Script entschieden, welche den ganzen ETL-Prozess abbilden.

Unter anderem besagt eine Transformationsregel, dass bei allen AS400 Strings die nachfolgenden Leerschläge abgetrennt werden müssen (RTRIM) und ein daraus resultierender Leerstring in NULL umgewandelt werden muss. Also ein klassischer Fall für eine UDF der Form:

CREATE FUNCTION dbo.CONVERT_NoString(@INPUT nvarchar(4000)) RETURNS nvarchar(4000) AS
BEGIN
  RETURN(CASE WHEN RTRIM(@INPUT) = “ THEN null
              ELSE                        RTRIM(@INPUT)
         END)
END

Ähnliche Funktionen gibt es z.B. auch für Geldbeträge oder für das Transformieren von Codes (wie z.B. Geschlecht, Beruf etc.) zwischen den Werten in den AS400 Daten und den Daten in der neu zu erstellenden SQL DB. Ein typisches UPDATE Statement sieht dann in etwa wie folgt aus:

UPDATE ExportDB.dbo.KLIBU_Tabelle1
SET BelegNummer = dbo.CONVERT_NoNumber(wvbs.BELNUM)
   ,Betrag      = dbo.CONVERT_NoMoney(wvbs.Betrag)
   ,VonDatum    = dbo.FORMDAT_Vers1(wvbs.VonDatum)
   ,BelegText   = dbo.CONVERT_NoString(wvbs.BuchungsText)
   ,StatusCode  = dbo.GetCodeElement(40, ‘1. AS400 Applikation’, wvbs.Status)
   …
FROM         ExportDB.dbo.KLIBU_Tabelle1 ExpTab1
  INNER JOIN ImportDB.dbo.AS400_Tabelle1 ImpTab1 ON ExpTab1.BasisID = ImpTab1.BasisID
WHERE bs.Herkunft = ‘1. AS400 Applikation’

Das funktioniert einwandfrei und ergibt bei Tabellen mit Einträgen von bis zu mehreren 10′000 Zeilen auch kaum Probleme mit der Performance. Ganz anders sieht es aus, wenn wie in meinem Fall eine einzige Tabelle rund 6.8 Millionen Zeilen hat und pro Zeile 14 solche und ähnliche UDFs aufgerufen werden. In meinem Fall dauerte dieser Update 6 Stunden und 20 Minuten! Und das wohlgemerkt während der Nacht, wo sonst der Server nur die Daumen dreht. Na ja, dafür hat der Server aber auch rund 95 Millionen UDFs aufgerufen; 6.8 Millionen Zeilen à 14 UDFs

Ich habe mir die Mühe gemacht und den Update Befehl umgeschrieben; diesmal ohne auch nur einem einzigen UDF Aufruf aber mit derselben Funktionalität. D.h. ich habe den UDF-Code genommen und diesen x-mal direkt in den Update Befehl dupliziert. Der Erfolg war bestechend. Neu benötigt der Update-Befehl noch lediglich 1 Stunde und 45 Minuten; diesmal jedoch unter Tags als sogar ein Team von 5 Mitarbeitern auf dem Server arbeitete!

Lesson learned: Setze UDFs wirklich nur ein, wenn zwingend notwendig und die Performance auch wirklich – aber wirklich – keine Rolle spielt!

Query Tuning – die Vorbereitung

„Der völlig entnervte Programmierer sitzt vor seinem Rechner und versucht verzweifelt seine T-SQL Queries umzuschreiben; solange bis die Performance endlich seinen Vorstellungen entspricht. Dann plötzlich der erleichterte Aufschrei – Geschafft! Feierabend! Am nächsten Morgen ist die Welt aber wieder gleich brutal wie am Vorabend; von performanten Queries ist weit und breit nichts zu sehen.“ Kommt dir diese Situation irgendwie bekannt vor? Wer hatte hier wohl seine Finger im Spiel?

Genau. Mit grosser Wahrscheinlichkeit hat dir hier das ausgeklügelte Caching Verhalten des SQL Servers einen Streich gespielt. Jeder von der Harddisk gelesener Datenblock kommt in einen Cache, genau so wie jedes kompilierte Query. Künftige Queries können von diesem Cache profitiere. Alles was der SQL Server aus dem Cache gebrauchen kann, wird er von dort holen. Lesen vom Cache (RAM) ist selbstverständlich um Faktoren schneller als lesen von der Disk oder gar das erneute kompilieren eines Queries.

Wenn du nun bei deinem Query Tuning immer wieder und wieder dasselbe Query – wenn auch leicht abgeändert – ausführst, wir der SQL Server schon bald einmal das Query alleine mit Hilfe des Caches ausführen können. Das Query wird scheinbar performanter. Darum: Vor jeder Tuning Iteration musst du unbedingt den Cache des SQL Servers leeren. Und so machst du dies:

CHECKPOINT
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS

DROPCLEANBUFFERS löscht die bereits gelesenen Harddiskblöcke aus dem Cache. Genau wie eine moderne Harddisk, schreibt auch der SQL Server nicht jeden modifizierten Datenblock augenblicklich auf die Disk. Vielmehr sammelt er weitere modifizierte Datenblöcke und schreibt dann zu gegebener Zeit alle Blöcke gemeinsam auf die Disk. Dies ergibt ein zusätzlicher Performance Boost. CHECKPOINT zwingt den SQL Server, diese Blöcke nun endlich auf Disk zu schreiben. So kann auch dieser Teil des Cache mit dem nachfolgenden DOPCLEANBUFFERS geleert werden. FREEPROCCACHE gibt die kompilierten Querypläne wieder frei. Jetzt ist der SQL Server wieder in einem definierten Zustand und du kannst mit der nächsten Tuning Iteration beginnen. Viel Erfolg!

SQL Server Hilfe auf Windows Life Search

Es kann nie genügend gute (!) Quellen für das Auffinden – nicht suchen – von Antworten und Lösungen zu Problemen rund um den SQL Server geben. Nicht etwa, weil SQL Server so ein schlechtes Produkt ist oder absolut unhandle bar wäre – nein, sondern weil einfach niemand alles alleine Wissen kann. Darum hast du ja auch meinen Blog gefunden

Und heute habe ich einen neuen Help-Point für SQL Server gefunden: http://search.live.com/macros/sql_server_user_education/booksonline

Das SQL Server Makro für Windows Life Search hat mich überzeugt: gute Treffer und schnelle Antwortzeit. Ich habe ihn sogleich in meine Hot Link Sammlung aufgenommen.

Was läuft auf dem Server?

Bestimmt hast du dich schon öfters gefragt, warum dein SQL Server wieder einmal so schlecht performt. In solch einem Fall wäre es doch wünschenswert zu wissen, welche SQL Kommandos so eben ausgeführt wurden; und das natürlich ad hoc ohne erst gross den Profiler oder andere Monitoring Tools starten zu müssen. Hierfür hat uns Microsoft mit dem SP3 für den SQL Server 2000 die Funktion fn_get_sql geliefert. Diese Funktion liefert unter anderem das letzte T-SQL Kommando für einen spezifischen Server Prozess (SPID). Im Gegensatz zu DBCC INPUTBUFFER kriegen wir damit aber nicht nur die ersten 255 Zeichen des Kommandos, sondern gleich das vollständige.

Mit folgendem T-SQL Script erfährst du im nu, was so auf deinem Server läuft:

SELECT sp.spid [SPID], sp.last_batch [Executed at], st.text [SQL]
FROM sys.sysprocesses sp
  OUTER APPLY fn_get_sql(sp.sql_handle) st
WHERE st.text IS NOT NULL

Meine Lösung  basiert auf einer Idee von Eli Leiba, welche in der April 2007 Ausgabe von SQL Server Magazine erschienen ist. Die dort vorgestellte Lösung basiert auf einer temporären Tabelle und einem Cursor. Weil mir Cursor aber ein greuel sind – Cursor sind Performance Killer sonder gleichen – habe ich mir die hier vorgestellte Lösung ausgedacht. Diese Kompakte Lösung (4 Zeilen anstelle von 26) ist nur dank dem OUTER APPLY Operator möglich; dieser steht erstmals mit dem SQL Server 2005 zur Verfügung. OK, wenn mein T-SQL Script so oder so nur auf dem SQL Server 2005 läuft, dann können wir die abgekündigte Funktion fn_get_sql (Upps, erst eingeführt und schon wieder veraltet!) auch gleich durch deren Ersatzfunktion sys.dm_exec_sql_text ersetzen:

SELECT sp.spid [SPID], sp.last_batch [Executed at], st.text [SQL]
FROM sys.sysprocesses sp
  OUTER APPLY sys.dm_exec_sql_text(sp.sql_handle) st
WHERE st.text IS NOT NULL

Das Ergebniss bleibt dasselbe wie mit der ersten Version. Viel Erfolg beim suchen deiner „Problemkinder“ – Big Brother is watching you!

PASS SWISS

Heute an den Microsoft TechDays 2007 in Zürich bin ich auf den schweizer Ableger der PASS – Professional Association for SQL Server – gestossen. PASS SWISS gibt es nun seit rund 6 Monaten. Gratulation!

Wenn du PASS bereits kennst, weisst du dass dort viel Wissen und Engagement rund um SQL Server Technologien vorhanden ist. Wenn aber PASS für dich Neuland ist, dann kann ich dir dort ein Vorbeischauen nur sehr empfehlen.

Ich wünsche mir sehr, dass auch PASS SWISS ein voller Erfolg wird. Als Starthilfe habe ich eine Referenz zur offiziellen Website von PASS SWISS auf meinen Blog gestellt. Toi, Toi, Toi.