Magic Trace – Die Anzahl der Verbindungen bestimmter Applikationen über Zeit verfolgen
Bestimmt hat schon mancher DBA das folgende Problem gehabt: Eine 3rd – Party – Applikation erstellt immer mehr Verbindungen und gibt diese nicht immer alle frei, so dass irgendwann interne Connection – Pools überlaufen oder es werden so viel offene Verbindungen erstellt, dass der SQL Server immer schlechtere Performance aufweist.
Es gibt eigentlich keine eingebauten oder mit SQL Server mitgelieferten Tools, welche die Fähigkeit besitzen, die Anzahl der Verbindungen bestimmter Applikationen über Zeit zu verfolgen und dies ggf. in einer tabelarischen Form zu präsentieren um die Daten später z. B. mit Excel auszuwerten.
Ein solches Tool kann mit internen Mitteln schnell und einfach gebaut werden.
Der 1. Schritt ist, eine Datenbank zu erstellen, in welche wir die Daten ablegen werden. Am einfachsten macht man dies auf dem gleichen Server, auf welchem der Trace laufen soll. Nennen wir diese Datenbank „MagicTrace“.
Der 2. Schritt ist, die notwendigen Datenstrukturen in der neuen Datenbank zu erstellen. Gemäß der Aufgabenstellung, brauchen wir eine Lookup - Tabelle für die Namen der verfolgten Applikationen (tlkpProgram) und eine Tabelle für die Zeitachse der Messungen (quasi die Dimensionstabelle, tblTime). Für die gemessenen Verbindungen benötigen wir die Fakt – Tabelle (tblMeasure), die sowohl auf Programme als Zeitachse über Fremdschlüssel verweist. Das Listing der Datenbankstruktur ist in der angehängten Datei „magic_trace_data.sql“ enthalten.
Im 3. Schritt müssen wir nun die Applikationslogik einspielen. Sie ist in der Datei „magic_trace_code.sql“ enthalten und besteht aus zwei Routinen: usp_checkpoint für die Analyse des gegenwärtigen Verbindungszustandes anhand der Systemtabelle „sysprocesses“ und usp_stats für die Anzeige der gesammelten Daten in denormalisierter, lesbarer, tabelarischer Form.
Nun muss man nur die Namen der gewünschten Applikationen, für die die Daten gesammelt werden sollen, in die Tabelle tlkpProgram eintragen (der korrekte Name kann aus der Systemtabelle „master..sysprocesses“, Feld „program_name“ ermittelt werden). Danach sollte man nur den Befehl „execute MagicTrace.dbo.usp_checkpoint“ in regelmäßigen Abständen ausführen (z. B. mit Hilfe eines SQL Server – Jobs, s. Anhang „MT-Job.sql“).
Die aufgerufene Routine ermittelt zunächst den aktuellen Zeitpunkt als Dimension, und als Fakten sammelt „select count(*)“ – Werte für alle Applikationen, deren Namen in tlkpProgram auffindbar sind.
Der Speicherbedarf für die Messungen ist sehr klein, da die Daten komplett normalisiert gespeichert werden. Im Prinzip werden für jede Messung „netto“ beansprucht: einmalig in der Dimensionstabelle 12 Bytes und in der Fakttabelle pro Applikation 20 Bytes, plus entspr. Overhead. Dadurch lassen sich über sehr lange Zeit die Daten in kleinen Zeitabständen sammeln, ohne dass man sich Sorgen um Speicherplatz machen muss.
Die Analyse kann jeder Zeit mit einem Aufruf von „execute MagicTrace.dbo.usp_stats“ gemacht werden. Dabei wird eine Tabelle präsentiert, die als 1. Spalte den Zeitstempel hat, und die sonstigen Spalten die Namen der betroffenen Anwendungen tragen. Dies wird u. a. dadurch erreicht, dass das Ergebnis als eine temporäre Tabelle dynamisch (mittels „alter table … add“) zusammengestellt wird. Wenn man möchte, kann man jetzt den Aufruf als externe Datenquelle in Excel anbinden und schöne und übersichtliche Grafiken erstellen.