Können Views die Performance des MSSQL Servers beeinträchtigen?

In diesem Beitrag berichten wir von einer Kundenanfrage bezüglich der Verwendung von Views in einer Datenbank. Zur kundenspezifischen Filterung von Datensätzen wurden Views verwendet, nach deren Einführung die Datenbankperformance deutlich abgenommen zu haben schien. Die Vermutung war, dass die relativ hohe Anzahl benötigter Views, ca. 1500, der Auslöser dieses Leistungsverlusts sein könnte.

An dieser Stelle möchten wir das Problem analysieren und Vorschläge zu einer Lösung geben.

Struktur der verwendeten Views

Die Views selektierten über 250 Spalten aus 8 miteinander verknüpften Tabellen und verwendeten hierbei Gruppierung group by, Vereinigungsmengen union und Eliminierung von Dubletten distinct. Jede der Views umfasste einen Code von nahezu 300 Zeilen. Einige der selektierten Spalten erforderten eine Umwandlung mittels

• CASE … WHEN … ELSE
• FLOOR()
• ISNULL()
• RTRIM()
• Bedingte Selektionen aus weiteren Tabellen

Die Views unterschieden sich lediglich durch eine where-Bedingung, durch die die Daten auf diejenigen Zeilen gefiltert wurden, die zu dem jeweiligen User gehörten. Man kann also auch formulieren, dass jeder User eine eigene View für seine spezifischen Daten besaß.

Testaufbau

Die Kundenanfrage bezog sich darauf, ob eine hohe Anzahl verwendeter Views die Ursache des beobachteten Leistungsverlust sein könne. Im Test wurde deshalb ausschließlich mit leeren Tabellen gearbeitet, um das Timing bei Verwendung der Views zu prüfen.

Es wurden Testreihen mit den vom Kunden verwendeten herkömmlichen Views und mit entsprechenden Views mit Schemabindung durchgeführt. In der Theorie sind diese Views performanter, weil hier die Abhängigkeiten der Views und die zugrunde liegenden Ausführungspläne nicht regelmäßig bezüglich Änderungen der zugrunde liegenden Objekte überprüft werden müssen.

Timing für die Ausführung der Views

Zur Messung der Performance wurde das schon oft an dieser Stelle von uns verwendete SQLQueryStress-Tool von Erik Ejlskov Jensen verwendet, das in Git zum Download verfügbar ist. Es wurden je Testreihe mehrere Tests durchgeführt und die mittleren Zeiten in der u.a. Tabelle erfasst.

Durchgeführte Testfälle

1. Einmalige Ausführung aller ca. 1500 Views. Hinweis: Bei der ersten Ausführung, nach dem Neustart der Instanz und nach dem Löschen des Instanz-Cache müssen die Views neu kompiliert werden. Dieser Vorgang umfasst nicht nur die Analyse des Statements, sondern auch die Bildung eines Ausführungsplans, sodass dieser Vorgang einen gewissen einmaligen Overhead erfordert.
2. Erneute Ausführung der Views. Dieser Test soll den in Testreihe 1 erwähnten Overhead für das Kompilieren und die Ermittlung von Ausführungsplänen zeigen.
3. Eine Benutzung aller 1500 Views mit 1500 potentiellen zugehörigen Benutzern ließ sich aus Zeit- und Resourcengründen nicht bewerkstelligen. Deshalb wurde in diesen Testläufen nach dem Löschen des Cache ein Test mit 100 Views aus 100 parallelen Sessions heraus durchgeführt.
4. Weitere Testläufe mit den 100 Views nachdem die Kompilierung nicht mehr erforderlich ist, da sie bereits in Schritt 3 erfolgte, bestätigen den Aufwand für das Kompilieren.
5. Als nächstes sollte ein Vergleich der Verarbeitung von Views gegenüber einem direkten Statement durchgeführt werden. Das Generieren entsprechender Statements hielten wir bei der Komplexität der Views (zur Erinnerung: ca. 300 Zeilen pro View) für zu Aufwändig, so dass als Alternative eine einzelne View in 100 Sessions 100 mal ausgeführt wurde.
6. Im letzten Testlauf wurde das select Statement, das zu der View der vorherigen Testläufen gehört, ebenfalls in 100 Sessions 100 mal ausgeführt.

Die folgende Tabelle zeigt die mittlere Dauer ohne und mit Schemabindung und abschließend zum Vergleich mit Testreihe 5 die direkte Ausführung des select-Statements.

ohne Schemabindung [Min]8:16,71682,86898:16,29887,069432,731534,9974

• Beim ersten Testlauf traten in beiden Fällen - also unabhängig von der Schemabindung, viele Exceptions (zwischen 50 und 90) auf mit der Meldung “A timeout occurred while waiting to optimize the query. Rerun the query.“ Diese Timeouts haben die extrem hohe Gesamtlaufzeit verursacht, die sich, zusammen mit der Kompilierung und Ermittlung der Ausführungspläne, in einem der Testläufe auf 1:21:16,1404 summiert hat. In unserem Assessment konnte die Verwendung von Sperrmechanismen durch einen extrem hohen Wert für das Wait-Ereignis RESOURCE_SEMAPHORE_QUERY_COMPILE bestätigt werden.

Alternativen

Als Alternative zur Verwendung des hier vorgestellten vom Kunden eingesetzten Konstrukts kann man sich folgende alternativen Mittel vorstellen:

• Nutzung von Row Level Security
• Setzen eines Session-Kontext bei der Anmeldung des Benutzers und Verwendung dieses Session-Kontext zur Filterung der benutzerspezifischen Daten in einer einzigen View die dann von allen Benutzern verwendet wird. Hierdurch würde der Pflegeaufwand bei evtl. Änderungen der Views von 1500 erheblich auf eins reduziert.

Fazit

Der vom Kunden beobachtete große Leistungsverlust bei der Verwendung sehr vieler Views konnte in unseren Tests nicht nachvollzogen werden. Bei Bedarf stehen aber möglicherweise Alternativen bereit. Die Leistung von Abfragen wird nicht dadurch verändert, dass man sie hinter einer View versteckt. Hier empfehlen wir jedoch zur Sicherheit, die Struktur und die Kosten des Ausführungsplans des SQL-Statements mit dem der entsprechenden View zu vergleichen.

Entgegen der Theorie bezüglich der Performance von Views bei Schemabindung zeigten diese eine geringfügig schlechtere Leistung. Die Ausführungsgeschwindigkeit eines direkt ausgeführten select Statements ist in etwa identisch mit der einer entsprechenden View, so dass es sich nicht lohnt, zwischen diesen beiden Möglichkeiten zu wechseln.

Die Nutzung von Views mit Schemabindung könnte in bestimmten Fällen - bei dem vorliegenden Kundenproblem leider nicht, da hier Outer-Joins verwendet wurden - extreme Geschwindigkeitsvorteile bieten, da in diesem Fall mit Indexed bzw. Materialized Views gearbeitet werden kann. Aber auch hier muss der jeweilige Fall genauer betrachtet werden. Grundsätzlich bieten Materialized Views (MVs) hohes Potential. Müssen die Daten der Tabellen, die der MV zugrunde liegen jedoch häufig aktualisiert werden, kann dies den Performancevorteil zunichte machen.

Wenn Sie mehr über dieses Thema erfahren möchten, stehen Ihnen unsere Expert:innen gerne zur Verfügung. Vereinbaren Sie gerne ein unverbindliches Beratungsgespräch über unser Kontaktformular.

Wir helfen Ihnen gerne weiter!