Sascha Lorenz aus Hamburg über die Themen Microsoft SQL Server, SharePoint, .NET Technologien und ganz allgemein über Business Intelligence.
Da der hypothetische Index (Hypothetical Index) vielen SQL Server Administratoren und Entwicklern bisher bewusst in freier Wildbahn noch nicht begegnet ist, habe ich mich mit der Kamera auf die Lauer gelegt und ihn tatsächlich auf Video festhalten können.
Viel Spaß beim Betrachten dieser interessanten und äußerst nützlichen Spezies!
Letzte Woche war ja die Microsoft Synopsis 2013 in Darmstadt.
Ist ein sehr gelungenes Event gewesen!
Ein Gespräch am Stand der deutschen SQL Server Community (PASS Deutschland e.V.) blieb mir besonders in Erinnerung. Dort hatte sich um die Popcorn Maschine ein kleines Grüppchen der SQL Server Szene gebildet. Im Gespräch ging es um “unbekanntere” SQL Server Features. Man kann bei einem so mächtigen Produkt wie dem SQL Server ja auch wirklich nicht mehr alles kennen. Ich warf eines meiner Lieblingsfeatures aus dieser Kategorie in den Ring: Den hypothetischen Index !
Nein, das ist kein brandneues Feature im SQL Server 20-something…
So gut wie alle Anwesenden hatten noch nie davon gehört/gelesen. Aufgrund des Namens wurde spekuliert und schnell geschlossen, dass es sich um einen virtuellen Index, also quasi nur um die Hypothese eines möglichen Index handeln müsste, der genutzt werden könnte, um zu prüfen, ob eine angedachte Optimierung eines Abfrageplans tatsächlich sinnvoll ist. Korrekt!
Was soll das alles? Klassische Optimierung ist doch häufig Trial & Error.
Bei der Optimierung eines Abfrageplans geht es ja meist um die Nutzung eines oder mehrerer Indizes, um beim Abarbeiten möglichst wenige 8 KB Seiten lesen zu müssen. Siehe dazu auch diesen Blogpost.
Es wird der gesamte Workload oder auch mal eine einzelne Query analysiert. Oft wird dann auf Basis des Abfrageplans eine Hypothese entwickelt mit welchen neuen Indizes die Query optimiert werden könnte. Das ist oft eine Wissenschaft für sich. Nur wie kann geprüft werden, ob der Query Optimizer auch wirklich den neuen Index nutzt? Der Optimizer ist ja recht wählerisch, was den Einsatz von Indizes angeht. Nur wie wählt der Optimizer aus dem Angebot an Indizes aus?
Hier kommt bei den meisten Entwicklern Trial & Error ins Spiel. Der Index wird einfach erstellt und es wird dann getestet. Ganz einfach, oder? Nun ja, das funktioniert bei überschaubaren Datenbanken durchaus problemlos. Dummerweise habe ich häufig mit recht großen Tabellen zu tun bei denen selbst die Erstellung eines Test Index mehr als eine Stunde dauern kann. Da will jeder neue Index gut überlegt sein und ist eigentlich etwas für ein explizites Wartungsfenster. Hier kommen wir mit Trial & Error nicht weiter. Ach ja, warum kann man nicht einfach mit einer Entwicklungsumgebung mit nur wenigen Datensätzen arbeiten? Das klappt leider nicht!
Hier kommen wir zu einer meiner Lieblingssätze für Optimierungscoachings: Was ist das Wichtigste am Index?
Die Statistiken ! Hinter jedem Index steht mindestens eine Statistik über die Inhalte also Daten der Indexstruktur. Statistiken führen leider oft ein Dasein im Schatten des Index. Immer wieder werden sie vernachlässigt und meist auch komplett missverstanden. Dabei ist die Erstellung und Pflege von Statistiken ein wesentlicher Teil jeder Performanceoptimierung, denn aufgrund der Statistiken wählt der Optimizer einen Index aus oder eben nicht.
Für eine Überprüfung benötigen wir also die Datenbestände aus dem Produktivsystem. Testdaten können das Ergebnis einer Performanceoptimierung komplett verfälschen. Das führt bei vielen Entwicklern zwar meist zu einer Sinnkrise, wenn sie davon erfahren, dennoch ist dem einfach so!
Wie kommen wir nun aus der Zwickmühle? Doch jedes mal ein halbes TB an Daten indizieren, um dann evtl. den Index gleich wieder zu verwerfen?
Hier kommt endlich der hypothetische Index ins Spiel. Er ist nur dafür da, um eine Hypothese zu überprüfen. Genaugenommen ist er gar nicht physisch vorhanden. Moment, wir brauchen aber den produktiven Datenbestand! Und genau hier kommt das Geniale. Beim hypothetischen Index werden NUR die Statistiken erzeugt. Das geht relativ flott und nimmt nur wenig Platz ein. Dann wird ein geschätzter Abfrageplan vom Queryoptimizer erstellt und man hat sofort Feedback, ob der geplante Index korrekt genutzt werden würde. Super, oder?
Gerade im Enterprise Data Warehouse Umfeld spart man damit wirklich sehr viel Zeit!
Nur wie muss man sich das jetzt in der Praxis vorstellen?
Eines noch vorweg: Es handelt sich hierbei um ein nicht dokumentiertes Feature! Also gibt es keinen Support und keine Garantie, dass das Feature in neuen Versionen oder nach einem Service Pack noch funktioniert.
Und wofür ist das Feature dann da? Für den Data Tuning Advisor (kurz DTA) ! Der nutzt das. Macht ja auch Sinn, oder?
Nun aber Butter bei die Fische. Wie geht das mit dem hypothetischen Index nun in der Praxis?
Als Beispiel nehmen wir mal wieder AdventureWorks2012 und meinen Liebling die FactInternetSales. Hier von eine leicht erweiterte Varianten um ein paar Zeilen mehr. Wie komme ich dazu? Siehe hier! Diese fis hat hier “nur” 12.079.600 Zeilen. Muss reichen für den Moment. ;-)
Dazu nehmen wir diese Query:
SELECT p.EnglishProductName,
d.WeekNumberOfYear,
d.CalendarYear,
AVG(fis.Freight) as 'AVG Freight',
sum(fis.SalesAmount) as 'SUM Salesamount'
FROM demo..FactInternetSales as fis
inner join AdventureWorksDW2012..DimProduct as p on
fis.ProductKey = p.ProductKey
inner join AdventureWorksDW2012..DimDate as d on
fis.OrderDateKey = d.DateKey
group by
p.EnglishProductName,
d.WeekNumberOfYear,
d.CalendarYear
order by p.EnglishProductName,
d.WeekNumberOfYear,
d.CalendarYear;
Für unsere Zwecke sollte sie reichen. Wie Ihr seht, nutze ich die FactInternetSales aus meiner Demo Datenbank und den Rest aus der original Adv2012.
Entscheidend wird der Teil des Queryplans sein, welcher die Faktentabelle lesen wird. Schauen wir uns den Plan einmal an.
Da werden also mal eben 357 MB an Daten gelesen. Okay, noch nicht die Welt, dennoch wollen wir genau das mittels Index optimieren.
Erst mal tun wie so als wüssten wir nix vom hypothetischen Index. Wir legen also nach kurzer Analyse einfach einen neuen Index an. Und zwar diesen hier:
CREATE NONCLUSTERED INDEX [hypIndex] ON [dbo].[FactInternetSales]
( [OrderDateKey] ASC )
INCLUDE ( [ProductKey], [SalesAmount], [Freight] )
Das Anlegen hat auf meinem Testsystem (virtuelle Maschine und so…) mal eben ganze 2-3 Minuten gedauert. Geht ja gar nicht…
Wie ist das Ergebnis?
Okay, wie erwartet nur noch wenige KB.
Optimiert ist! Mit Trial & Error…
Kommen wir zum zweiten Teil. Nun das Ganze mit einem hypothetischen Index. Also den jetzigen löschen… und? Ja, wie legt man nun so einen Index an?
Wie gesagt, wir brauchen ja NUR die Statistiken. Daher ist die magische Erweiterung auch: “WITH STATISTICS_ONLY = –1”. Nicht wundern, dass das SSMS ihn nicht erkennt. Ist ja auch undokumentiert.
Dennoch, oh Wunder, SQL Server hat es gefressen.
Und es hat gefühlt nur 1 Sekunde gedauert. Genau hier ist ja unsere Zeitersparnis!
Nur ein Blick ins SSMS lässt uns zweifeln, ob das überhaupt geklappt hat.
Unser Index ist gar nicht da!!! Was soll das denn nun?
Kurzer Blick in die Statistiken zeigt dennoch, dass da was passiert sein muss:
Schauen wir uns das Ganze mal mit einem Catalogview an:
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID('[dbo].[FactInternetSales]')
Hier taucht unser Index auf. Super. Und wenn man ein wenig zur Seite scrollt, dann findet man eine Spalte “is_hypothetical”. Ehrlich, die war immer schon da! Versprochen! :-)
SELECT is_hypothetical,*
FROM sys.indexes
WHERE object_id = OBJECT_ID('[dbo].[FactInternetSales]')
Hier kurz nach vorne geholt:
Unheimlich, oder? Und es geht noch weiter. Der Queryoptimizer nutzt den Index nämlich noch nicht.
Wie bringen wir ihn nun dazu? Dazu gibt es den nächsten undokumentierten Befehl: DBCC AUTOPILOT().
DBCC AUTOPILOT(0, 9, 261575970, 3)
GO
Was sind denn das für Parameter?
9 ist die Datenbank ID. Die gibt es u.a. so: SELECT DB_ID() AS DBID.
261575970 ist die Objekt ID. Siehe einfach oben.
3 ist die Index ID. Siehe nochmals oben. :-)
Und nun? Es wird noch besser…
SET AUTOPILOT ON
GO
Wenn wir nun den geschätzten Queryplan abfragen, bekommen wir:
Also der Queryoptimizer schlägt den hypIndex vor, obwohl dieser gar nicht existiert. Wir sehen auch, dass die Schätzung zwar sehr genau ist, aber er hat sich um ein paar KB vertan. Ich finde das ist okay, oder?
Und was passiert im AUTOPILOT Modus, wenn wir die Query einfach laufen lassen?
Fast nix anderes, denn der SQL Server führt nun keine Queries mehr aus in dieser Session. Wir bekommen nur noch geschätzte Pläne zurück.
Mit SET AUTOPILOT OFF schaltet Ihr den SQL Server wieder zurück. Und löschen geht wie gewohnt mit:
DROP INDEX hypindex ON dbo.FactInternetSales;
Natürlich ist das immer noch Trial & Error. Nur wesentlich optimierter.
So, damit wären wir auch schon am Ende dieses Posts. Hmm, irgendwie länger als geplant geworden, dafür mit Demo Teil. :-)
Ich wünsche Euch viel Spaß beim experimentieren mit Euren hypothetischen Indizes!
Wie schon mal geschrieben, ist ja am 10. und 11.4.2013 die Microsoft Konferenz Synopsis 2013.
Auf dieser könnt Ihr Euch über aktuelle Microsoft-Produkte wie Windows Server 2012, SQL Server 2012, System Center 2012, Exchange und SharePoint 2013 sowie Windows Azure zu informieren! Wo gibst das schon sooo geballt.
Zu den Highlights der Konferenz gehören sicherlich auch die nachmittäglichen IT Camp Breakouts, die Themen wie Private Cloud und Dynamic Infrastructure in den Blick nehmen. Für diese könnt Ihr Euch direkt nach der Anmeldung zur Synopsis registrieren, da die Plätze wiederum begrenzt sind.
by the way, gerade gesehen. Leser des TechNet Newsletters bekommen einen Promo-Code mit 100 Euro Anmeldegebühr. Da Ihr ja alle den Newsletter abonniert habt, hier der Promo-Code "TECHNET2013". :-)
Wir sehen uns auf der Synopsis in Darmstadt!
Diese Woche ist es soweit. Andreas Wolter kommt nach Hamburg in die SQL Server Usergroup (PASS Deutschland e.V.) und hält einen Vortrag zum Thema Extended Events im SQL Server 2012.
Details zum Treffen hatte ich schon mal hier gepostet. Nicht verpassen, wir hatten beim letzten Treffen über 30 Teilnehmer gehabt.
Sofern das Thema für Euch komplett neu ist und Ihr noch nicht genau einschätzen könnt, ob das für Euch etwas ist, dann empfehle ich für den schnellen Hunger zwischendurch meine Einsteiger Webcasts auf Youtube.
Als Fortsetzung zu meinem letzten Cast habe ich einen weiteren aufgenommen, da es diverse Fragen gab.
Ich hatte ja den SSIS Balanced Data Distributor von Microsoft gezeigt und erläutert wie ein Ansatz für die Nutzung für die parallele Sortierung von Daten aussehen könnte.
Bezüglich meiner verwendeten Skript Komponenten kamen Fragen auf, welche ich hier nun beantworten möchte:
Ich hoffe, dass ich damit alle (Un)Klarheiten beseitigen konnte. :-)
Wenn nicht, dann wieder fragen. Okay?
Es ist wieder soweit. Die Einladung für das Treffen der Hamburger SQL Server Usergroup Hamburg (PASS Deutschland e.V.) ist raus.
Wir treffen uns am 13.3.2013 um 18:30 in der Microsoft Niederlassung Hamburg.
Microsoft Deutschland GmbH
Geschäftsstelle Hamburg
Gasstraße 6a
22761 Hamburg
Letzten Monat hatten wir über 30 Teilnehmer vor Ort! Ihr seid die Community!
Im März haben wir wieder ein ganz besonders spannendes Thema für Euch!
Vortrag: “Hasta la vista, Profiler.” - Einführung in Tracing mit Extended Events.
Extended Events, obwohl bereits seit SQL Server 2008 im Produkt enthalten, ware bisher nur wenigen Eingeweihten als extrem performantes und extrem flexibles Tracing-Framework in SQL Server bekannt. Seit SQL Server 2012 ist für dieses wichtige Feature, was SQL Trace/Profiler ersetzen wird, auch in SSMS verfügbar.
In dieser Einführungssession werden wir uns insbesondere die System Health Session ansehen, und wie man diese für Deadlock-Analysen verwenden kann. („Hasta la vista, Profiler. Hasta La vista Traceflags 1204, 1222“)
Kommentar von mir: Wieder nicht verpassen! Microsoft wird sich in einer der nächsten Versionen vom SQL Server Profiler (SQL Trace) trennen, daher wird es für alle (DBA, DEV & BI’ler) Zeit sich mit dem „neuen“ Tracing Tool vertraut zu machen!
Sprecher ist: Andreas Wolter.
Andreas Wolter ist einer von weltweit weniger als 100 Microsoft Certified Master SQL Server 2008 (MCM) + MCT, MCITP:DD/DA/BID, MCDBA, MCSA und kann auf über zehn Jahre Erfahrung als Trainer und Consultant für SQL Server zurückblicken. Seit einigen Jahren ist er aktives Mitglied der PASS und Sprecher auf Fachkonferenzen in Deutschland und den USA. Mit seiner Firma SARPEDON Quality Lab (www.SarpedonQualityLab.com ) hat er sich besonders auf die Entwicklung und Optimierung von Datenbank- und Datawarehouse-Architekturen spezialisiert. Sein Blog ist unter http://www.vb-magazin.de/forums/blogs/andreaswolter zu finden.
Anschrift sonst auch im beigefügtem Termin für Outlook (etc.).
Kostenlose Parkplätze befinden sich hinter dem Gebäude. Der Parkplatz ist über die Rampe mit dem Schild „Microsoft Kunden“ erreichbar.
Nur wenige Minuten zu Fuß ist der S-Bahnhof Bahrenfeld entfernt (S1/S11).
Ansprechpartner vor Ort: MS Empfangs-Team und Sascha Lorenz
Wir bitten um eine vorherige Anmeldung per Email an: slo@sqlpass.de
Wichtig: wir benötigen die Anmeldungen 2 Tage vor dem Treffen, da wir uns 2012 bei Microsoft treffen können und dort Besucherausweise ausgestellt werden! Spontane Teilnehmer sind dennoch willkommen. ;-)
Beim letzten PASS Treffen in Hamburg kam ich während des Vortrags von Oliver Engels und Tillmann Eitelberg auf eine Idee, welche wir auch kurz spontan vor Ort diskutiert haben und mir keine Ruhe ließ.
Ohne weitere Recherche, ob einer der weltweiten Community Kollegen bereits ähnliches publiziert hatte, habe ich mich an meinen Rechner gesetzt.
Es geht um die Leistungsfähigkeit der SSIS eigenen Sort Komponente und inwiefern es möglich wäre durch eine Last Verteilung mit dem Microsoft Balanced Data Distributor eine Performance Verbesserung zu erreichen.
Meine Tests haben nun ergeben, dass es tatsächlich funktioniert und ich 50% Performance Gewinn erreichen konnte. Anbei das Video, welches ich spontan dazu gemacht habe mit allen Erläuterungen.
Balanced Data Distributor für SQL Server 2008 / 2008 R2
http://www.microsoft.com/en-us/download/details.aspx?id=4123
Balanced Data Distributor für SQL Server 2012
http://www.microsoft.com/en-us/download/details.aspx?id=30147
Sofern Ihr Euch für Master Data Management interessiert und mehr über die technische Realisierung mit den SQL Server Master Data Services erfahren möchtet, dann kann ich Euch u. a. die Wiederholung meines PASS Essentials über MDS 2012 empfehlen.
Wir, die Deutsche SQL Community PASS, führt dieses am
22.03.2012 in Berlin bei dem Raumsponsor Beta Systems Software AG durch.
Preis für Mitglieder des PASS Deutschland e.V. inkl. MwSt. ist 299€. Damit sehr günstig.
Weitere Details als PDF Flyer findet Ihr hier.
Als Teaser für die Inhalte empfehle ich die Aufzeichnung meines Vortrags über Master Data Services auf den SQL Server Launch Event letztes Jahr in Köln.
Sofern Ihr dieses Essential auch in Eurer Region (Süd zum Beispiel) durchgeführt haben möchtet, spricht mich einfach an. Da können wir als SQL Community bestimmt was organisieren.
Erinnert Ihr Euch noch an das coole SQL Server 2012 Launch Event letztes Jahr in Köln? Ja? Oder Ihr konntet nicht dabei sein und schaut seither sehnsüchtig die aufgezeichneten Casts auf TechNet? Meinen zum Beispiel zum Thema Master Data Services ? :-)
Dann habe ich gute Neuigkeiten für Euch! Microsoft kommt auch dieses Jahr mit einer eigenen Konferenz daher und es geht wieder um Server (u. a. SQL Server). Dieses Mal in Darmstadt. Hier der offizielle Text dazu:
“Starten Sie mit uns in eine neue Ära: Am 10. und 11. April 2013 präsentieren wir in Darmstadt die erste Microsoft Server- und Cloud-Konferenz für neue Perspektiven in IT und Business.
An zwei Konferenztagen präsentieren wir Ihnen Kundenprojekte und Erfahrungsberichte von Microsoft Partnern, Microsoft Consulting Services und unabhängigen Experten. Erleben Sie in Live-Demos und spannenden Vorträgen, welche Möglichkeiten und Chancen Ihnen die neueste Generation der Microsoft Server- und Cloud- Technologien bietet. Im Mittelpunkt stehen dabei Unified Device-Management, Big Data Analytics und Business Intelligence, Productivity und Collaboration, Datenmanagement sowie Private, Public und Hybrid-Cloud. Egal ob IT-Entscheider oder IT-Pro – Synopsis 2013 ist ein Pflichttermin für alle, deren IT-Strategie Microsoft-Server-Lösungen beinhaltet. Informieren Sie sich jetzt und sichern Sie sich den Frühbucherrabatt unter www.microsoft-synopsis.de.”
Das liest sich doch so als gebe es da keinen Weg dran vorbei, oder?
Ich werde auch wieder vor Ort dabei sein und einen Vortrag halten. Hier der Link auf die Agenda. Gemeinsam mit dem Markus Thomanek von Microsoft werde ich über Datenqualität mittels Data Quality Services, Master Data Services (Das Thema lässt mich wohl nicht mehr los…) und einer Prise Integration Services sprechen.
.jpg)
Am Mittwoch, den 9.1.2013, ist es wieder soweit. Das erste Treffen der Hamburger SQL Server Usergroup. Wir treffen uns wieder in der Hamburger Microsoft Niederlassung.
Thema sind die Master Data Services. Den Vortrag halte ich dieses Mal selbst. Hier der Abstrakt des Vortrags:
“In diesem Vortrag es um die Aktualität und Qualität von Daten im Unternehmen.
Mit den Master Data Services stellt Microsoft ein mächtiges Master Data Management Werkzeug zur Verfügung, welches quasi out-of-the-box eingesetzt werden kann für die Pflege von Daten.
Nur was ist Master Data Management? Was sind überhaupt Master Data? Stammdaten?
Wir betrachten sowohl operatives als auch analytisches MDM. Es wird die Arbeit mit den MDS vorstellt. Die Weboberfäche als auch das neue Excel Add-In betrachtet. Wie können MDM Prozesse manuell bzw. auch automatisiert durchgeführt werden? Wie werden Data Stewards in den Fachabteilungen effektiv eingebunden? Und welche Rolle spielt Security dabei?
Der Vortragende nutzt die MDS seit dem SQL Server 2008 R2, hat viele Erlebnisse als auch Erfahrungen aus zahlreichen Proof-of-Concepts für Microsoft Kunden sammeln dürfen, führt PASS Essentials Events zu den MDS durch und hat die MDS auch auf dem Kölner SQL Server 2012 Launch Event vorstellen dürfen.”
Wir treffen uns um 18:30 und haben bis ca. 20:30 Zeit um uns über das Thema auszutauschen. Hier noch mal die Koordinaten der Hamburger Microsoft Niederlassung:
Microsoft Deutschland GmbH
Geschäftsstelle Hamburg
Gasstraße 6a
22761 Hamburg
Kostenlose Parkplätze befinden sich hinter dem Gebäude. Der Parkplatz ist über die Rampe mit dem Schild „Microsoft Kunden“ erreichbar.
Nur wenige Minuten zu Fuß ist der S-Bahnhof Bahrenfeld entfernt (S1/S11).
