Mein letzter Blogeintrag zum Thema Repository im Data Warehouse scheint einige Fragen aufgeworfen zu haben!

Neben dem eigentlichen Thema "Repository", danke für die Rückmeldungen, kam oft die Frage, wie ich denn nun aus einer SQL Query die in dieser verwendeten Spalten der Quelltabellen auslese und was der Queryplan damit zu tun hat. Daher hier jetzt ein paar mehr Informationen dazu.

Die Vorgehensweise ist ein Abfallprodukt aus der Entwicklung eines Performancetuning Tools in einem Projekt.

Kurz zur Sicherheit erwähnt, wofür ich sowas in Projekten überhaupt benötige. Es geht um die Aufgabenstellung, dass ich hunderte von Queries habe und sehr gerne "auf-Knopfdruck" sehen möchte, welchen Einfluss eine Änderung am Datenmodell auf eben diese Queries hätte. Woher habe ich diesen Berg Abfragen? Entweder aus einem Profiler Trace einer OLTP Anwendung oder aus RDL-Dateien. Auch schon gemacht, daher hier als möglich aufgezählt, die Quellen in SSIS Paketen und Analysis Services Projekten. Auf jeden Fall sind die von einer Lösung verwendeten Queries immer gute Kandidaten in einem entsprechenden Repository gespeichert zu werden.

Gerne empfehle ich ISVs auch ein solches Repository als Grundlage einer Dokumentation zu führen und vor Änderungen am Datenmodell wie hier beschrieben vorzugehen. Damit fängt ein gutes Qualitätsmanagement doch erst an. Das Performancetuning kann dann gleich "daneben" gepflegt werden. So sah übrigens das ursprüngliche Projekt aus.

Die Aufgabenstellung nach den verwendeten Spalten hatte ich schon oft, aber häufig ist der erste Gedanke der Kunden, dass man dafür doch einen separaten SQL Parser bräuchte. Nun ja, sicherlich machbar, also mit entsprechend Zeit usw., aber nutzen wir doch den SQL Server als Tool dafür!

Nehmen wir mal eine einfache Query für das AdventureWorksDW2008

select SUM(salesamount) as summe
from dbo.FactInternetSales f
left join dbo.DimCustomer c on f.CustomerKey = c.CustomerKey
where c.HouseOwnerFlag = '0'

Mittels SET SHOWPLAN_XML ON bringe ich den SQL Server dazu, dass dieser.

a.) die Query nicht ausführt
b.) mir den geschätzten Ausführungsplan im XML Format liefert

Schon mal total toll. Nur bringt es mich überhaupt nicht weiter, da ich den Prozess ja automatisieren möchte. Also Management Studio zu, Visual Studio auf und ein wenig C#.

SqlConnection con = new SqlConnection(<ConnectionString>);
con.Open();
SqlCommand showplan_on = new SqlCommand("set showplan_xml on", con);
showplan_on.ExecuteNonQuery();

SqlCommand query = new SqlCommand(<Query>, con);
SqlDataReader showplan_results = query.ExecuteReader();
if (showplan_results.Read())
    tbOutput.Text = showplan_results[0].ToString();
showplan_results.Close();

SqlCommand showplan_off = new SqlCommand("set showplan_xml off", con);
showplan_off.ExecuteNonQuery();
con.Close();

Und schon habe ich den geschätzten Ausführungsplan erhalten und zwar ohne, dass meine Query ausgeführt wurde!

Mit so einem Ausführungsplan kann man nun sehr spannende Sachen machen, aber für unseren Fall hier interessieren uns nur die Elemente mit dem Namen ColumnReference. Da stecken nämlich unsere Spalten drin. Wie kommen wir da nun ran? Der Ausführungsplan ist bereits geladen. Aber er ist noch als String verpackt. Das kann das .net Framework aber besser, daher nix wie ab in eine XML Datenstruktur und die Elemente gesucht.

XmlDocument dom = new XmlDocument();
dom.LoadXml(tbOutput.Text);

XmlElement root = dom.DocumentElement;

XmlNamespaceManager nsmgr = new XmlNamespaceManager(dom.NameTable);
nsmgr.AddNamespace("bk", "http://schemas.microsoft.com/sqlserver/2004/07/showplan");

XmlNodeList nodelist = root.SelectNodes("//bk:ColumnReference[@Database]", nsmgr);

Und wir haben eine NodeList mit unseren Elementen! Diese können wir nun ausgeben.

xn.Attributes.GetNamedItem("Database").InnerText + "." + xn.Attributes.GetNamedItem("Schema").InnerText + "." + xn.Attributes.GetNamedItem("Table").InnerText + ".[" + xn.Attributes.GetNamedItem("Column").InnerText + "]"

Da haben wir unsere Spalten! War doch ganz einfach, oder?

Und das funktioniert dann natürlich nicht nur mit SELECT Statements! Hier mal ein Beispiel für ein UPDATE Statement mit Unterabfragen.

Ergibt folgende Ausgabe:

Mit dieser Vorlage sollte sich eigentlich jeder sein eigenes Tool für diese Art von Fragestellung bauen können. Viel Spaß damit!