Warum sollte SET ARITHABORT ON eine Abfrage drastisch beschleunigen?

Ein wenig veraltet, aber für alle, die hier landen ein ähnliches Problem …

Ich hatte das gleiche Problem. Für mich stellte sich heraus, dass es sich um Parameter-Sniffing handelte, was ich zunächst nicht genug verstand, um mich darum zu kümmern. Ich fügte einen „Set Arithabort On“ hinzu, der das Problem behebte, aber dann kam es zurück. Dann las ich:

http://www.sommarskog.se/query-plan-mysteries.html

Es wurde alles gelöscht. Weil ich Linq verwendet habe, um SQL und hatte nur begrenzte Optionen, um das Problem zu beheben. Am Ende verwendete ich einen Abfrageplan-Leitfaden (siehe Ende des Links), um den gewünschten Abfrageplan zu erzwingen.

Kommentare

• Mehr als sechs Jahre später ist der in dieser Antwort angegebene Link immer noch “ erforderlich, um “ zu lesen … und immer noch aktuell auch, die letzte Revision ist Dezember ‚ 17.

.NET-Anwendungen stellen eine Verbindung mit der standardmäßig deaktivierten Option her, die jedoch in Management Studio standardmäßig aktiviert ist. Das Ergebnis ist, dass der Server tatsächlich 2 separate Ausführungspläne für die meisten / alle Prozeduren zwischenspeichert. Dies wirkt sich darauf aus, wie der Server numerische Berechnungen durchführt. Daher können Sie je nach Verfahren sehr unterschiedliche Ergebnisse erzielen. Dies ist wirklich nur eine von zwei gängigen Methoden, mit denen ein Proc einen schrecklichen Ausführungsplan erhalten kann. Die andere ist das Parameter-Sniffing.

Schauen Sie sich https://web.archive.org/web/20150315031719/http://sqladvice.com/blogs/gstark/archive/2008/02/12/Arithabort-Option-Effects-Stored-Procedure-Performance.aspx für ein wenig mehr Diskussion darüber.

Kommentare

• Ich stimme der Hälfte von zu diese Antwort. Ich bin jedoch sehr skeptisch gegenüber dem numerischen Berechnungsanspruch!
• @Martin: Ich glaube, ich war unklar. Ich habe nur gesagt, dass ARITHABORT ON SQL Server bei jedem div / 0- oder arithmetischen Überlauffehler fehlerhaft macht. Wenn es ausgeschaltet ist, geht es weiter und kann aus irgendeinem Grund alle möglichen schrecklichen Probleme verursachen.
• @Ben – Ja, tut mir leid, ich wollte ‚ nicht besonders angreifen Ihre Antwort Ich habe nur darauf hingewiesen, dass es sehr einfach ist, eine SET -Option zu ändern, um einen besseren Plan zu erhalten und dies als die fehlerhafte Option selbst zu diagnostizieren. Ich ‚ bin nicht überzeugt, dass der Typ in Ihrem Link ‚ dies nicht getan hat.
• @Martin – Not a Problem, ich habe ‚ nicht gedacht, dass Sie mich angreifen. Die andere Diskussion, die ich verlinkt habe, könnte etwas unklar sein. Ich habe nur versucht, Belege zu liefern.
• @Martin Im Nachhinein glaube ich, dass Sie richtig sind.

Ich würde argumentieren, dass dies mit ziemlicher Sicherheit Parameter-Sniffing war.

Es wird häufig angegeben, dass SET OPTIONS die Leistung auf diese Weise beeinträchtigen kann, aber ich habe noch keine einzige maßgebliche Quelle für diese Behauptung gefunden, außer in dem Fall, in dem Sie sich befinden Verwenden von indizierten Ansichten / persistierten berechneten Spalten.

In diesem Fall (für SQL2005 + und , es sei denn, Ihre Datenbank befindet sich im SQL2000-Kompatibilitätsmodus ). Wenn Sie sowohl ARITHABORT als auch ANSI_WARNINGS OFF haben, wird der Index nicht verwendet Möglicherweise wird also eher ein Scan als die gewünschte Suche durchgeführt (und ein gewisser Overhead, da das Ergebnis der dauerhaften Berechnung nicht verwendet werden kann). ADO.NET scheint standardmäßig ANSI_WARNINGS ON aus einem Schnelltest zu haben, den ich gerade durchgeführt habe.

Die Behauptung in Bens Antwort , dass „die Art und Weise, wie der Server numerische Berechnungen durchführt“, Minuten zu einem Ergebnis hinzufügen kann, das sonst weniger als eine Sekunde dauern würde, scheint mir einfach nicht glaubwürdig. Ich denke, was dazu neigt, ist, dass bei der Untersuchung eines Leistungsleistungsproblems Profiler verwendet wird, um die fehlerhafte Abfrage zu identifizieren. Dies wird in das Management Studio eingefügt und ausgeführt und liefert sofort Ergebnisse. Der einzige offensichtliche Unterschied zwischen Verbindungen ist die Option ARITH_ABORT.

Ein schneller Test in einem Management Studio-Fenster zeigt, dass das Leistungsproblem erneut auftritt, wenn SET ARITHABORT OFF aktiviert und die Abfrage ausgeführt wird, sodass der Fall anscheinend geschlossen ist. In der Tat scheint dies die Fehlerbehebungsmethode zu sein, die im Link Gregg Stark verwendet wird.

Dabei wird jedoch die Tatsache ignoriert, dass Sie mit dieser Option festlegen können Am Ende erhalten Sie genau den gleichen schlechten Plan aus dem Cache .

Diese Wiederverwendung des Plans kann auch dann erfolgen, wenn Sie als anderer Benutzer als die von der Anwendungsverbindung verwendete angemeldet sind.

Ich habe dies getestet, indem ich zuerst eine Testabfrage aus einer Webanwendung und dann aus dem Management Studio mit SET ARITHABORT OFF ausgeführt habe. Die Anzahl der Benutzer wurde anhand der folgenden Abfrage erhöht.

SELECT usecounts, cacheobjtype, objtype, text ,query_plan FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle) 

Damit diese gemeinsame Nutzung von Plänen tatsächlich stattfinden kann, müssen alle Plan-Cache-Schlüssel gleich sein. Neben arithabort selbst benötigen die ausführenden Benutzer dasselbe Standardschema (wenn die Abfrage auf impliziter Namensauflösung beruht) und die Verbindungen dasselbe language set.

Eine ausführlichere Liste der Plan-Cache-Schlüssel hier

Ich weiß, dass ich zu spät zu dieser Party komme, aber für zukünftige Besucher ist Martin genau richtig. Wir sind auf dasselbe Problem gestoßen – ein SP lief sehr langsam Für .NET-Clients war es für SSMS sehr schnell. Bei der Untersuchung und Lösung des Problems haben wir die systematischen Tests durchgeführt, nach denen Kenny Evitt in seinem Kommentar zu Martins Frage fragt.

Verwenden einer Variante von Bei Martins Abfrage suchte ich im Prozedur-Cache nach dem SP und fand zwei davon. Bei den Plänen war tatsächlich ARITHABORT ON und ARITHABORT OFF aktiviert. Die ARITHABORT OFF-Version hatte eine Indexsuche während die ARITHABORT ON-Version einen Index-Scan für verwendete die gleiche Ausgabe. Angesichts der beteiligten Parameter hätte die Indexsuche eine Suche nach zig Millionen Datensätzen für die Ausgabe erforderlich gemacht.

Ich habe die beiden Prozeduren aus dem Cache gelöscht und den SP vom .NET-Client erneut ausführen lassen die gleichen Parameter (die einen weiten Datumsbereich für einen Kunden mit viel Aktivität enthielten). Der SP kehrte sofort zurück. Der zwischengespeicherte Plan verwendete denselben Index-Scan, der zuvor im ARITHABORT ON-Plan enthalten war – diesmal war der Plan jedoch für ARITHABORT OFF vorgesehen. Wir haben den SP mit denselben Parametern in SSMS ausgeführt und sofort wieder Ergebnisse erhalten. Jetzt haben wir gesehen, dass ein zweiter Plan für ARITHABORT ON mit dem Index-Scan zwischengespeichert wurde.

Wir haben dann den Cache geleert, den SP in SSMS mit einem engen Datumsbereich ausgeführt und ein sofortiges Ergebnis erhalten. Wir haben festgestellt, dass der resultierende zwischengespeicherte Plan eine Indexsuche hatte, da dieselbe Ausgabe zuvor mit einem Scan behandelt wurde (was auch eine Suche im ursprünglichen Plan mit ARITHABORT OFF war). Wieder von SSMS aus haben wir den SP ausgeführt, diesmal mit demselben großen Datumsbereich, und haben dieselbe schreckliche Leistung gesehen, die wir in der ursprünglichen .NET-Anforderung hatten.

Kurz gesagt, die Ungleichheit hatte nichts damit zu tun Der tatsächliche Wert von ARITHABORT – wenn dieser von beiden Clients aktiviert oder deaktiviert ist, können wir eine akzeptable oder schreckliche Leistung erzielen: Alles, was zählt, sind die Parameterwerte, die beim Kompilieren und Zwischenspeichern des Plans verwendet werden.

Während MSDN zeigt an, dass ARITHABORT OFF selbst einen negativen Einfluss auf die Abfrageoptimierung haben kann. Unsere Tests bestätigen, dass Martin korrekt ist – die Ursache war Parameter-Sniffing und der resultierende Plan war nicht optimal für alle Parameterbereiche.

Kommentare

• Ich frage mich, was dieser Ausdruck Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues. bedeutet. Ob es sich nur um die Unfähigkeit handelt, Indizes für berechnete Spalten und Ansichten zu verwenden (wenn ANSI_WARNINGS ebenfalls deaktiviert ist) oder ob dies tatsächlich einen anderen Effekt hat.
• Ich ‚ bin mir nicht sicher. Ich frage mich, ob es ‚ einfach der Fall ist, dass jemand bei MSDN in eine ähnliche Situation geriet, ARTIHABORT auf ON setzte, die Leistungsverbesserung sah und zu denselben Schlussfolgerungen kam, die andere haben. Was indizierte Ansichten und berechnete Spalten betrifft, bin ich ‚ unklar. An einer Stelle heißt es, dass die SET-Optionen bestimmte Werte haben müssen, wenn eine INSERT-, UPDATE- oder DELETE-Operation die darin gespeicherten Datenwerte ändert. An anderer Stelle wird angegeben, dass der Optimierer Indizes für “ jede Abfrage “ ignoriert, die auf die indizierte Ansicht oder berechnete Spalte verweist. Sind beide wahr oder ist es wirklich “ eine Abfrage, die Daten ändert „?

Hatte gerade dieses Problem. Wie hier gesagt, ist die Hauptursache mehrere Abfragepläne, von denen einer nicht optimal ist. Ich wollte nur überprüfen, ob ARITHABORT das Problem tatsächlich selbst verursachen kann (da die Abfrage, mit der ich Probleme hatte, keine Parameter hatte, wodurch das Parameter-Sniffing aus der Gleichung entfernt wird).

Dies erinnert mich an genau das Problem, das ich in den Tagen von SQL Server 2008 hatte. In unserem Fall haben wir plötzlich festgestellt, dass ein SQL-Job plötzlich langsamer wurde (normalerweise einige Sekunden und jetzt mehr als 9 Minuten). Der Job muss auf einen Verbindungsserver zugreifen. Wir haben ARITHABORT im Schritt des Jobs aktiviert, und es schien Das Problem wurde für einige Tage gelöst und kehrte dann zurück.

Wir haben später ein Ticket mit MS-Unterstützung geöffnet, und anfangs können sie es auch nicht herausfinden, und das Ticket wurde an ein sehr hochrangiges PFE-Team und zwei weitergeleitet Support-PFEs versuchten, dieses Problem herauszufinden.

Der letzte Grund ist, dass die Benutzeranmeldeinformationen (um den Jobschritt auszuführen) nicht auf die Statistiken der zugrunde liegenden Tabellen (auf der Seite des Verbindungsservers) zugreifen können und daher der Ausführungsplan nicht optimiert ist.

Im Detail hat der Benutzer keine Berechtigung für DBCC SHOW_STATISTICS (obwohl der Benutzer AUSWÄHLEN kann Der Tisch). Gemäß MSDN wird diese Berechtigungsregel nach SQL 2012 SP1

Berechtigungen für SQL Server und SQL-Datenbank

Um das Statistikobjekt anzeigen zu können, muss der Benutzer die Tabelle besitzen oder der Benutzer muss Mitglied der festen Serverrolle sysadmin, der festen Datenbankrolle db_owner oder der festen Datenbankrolle db_ddladmin sein.

SQL Server 2012 SP1 ändert die Berechtigungsbeschränkungen und Ermöglicht Benutzern mit SELECT-Berechtigung die Verwendung dieses Befehls. Beachten Sie, dass die folgenden Anforderungen vorhanden sind, damit SELECT-Berechtigungen zum Ausführen des Befehls ausreichen:

Um dieses Problem zu überprüfen, müssen Sie nur den Profiler auf der Instanz des verknüpften Servers ausführen und einige Ereignisse unter „Fehler“ aktivieren und Warnungen „wie unten gezeigt.

Hoffe, diese Erfahrung kann der Community irgendwie helfen.