Hypothetischer Index im SQL Server, was soll das denn sein?

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!